ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Åý¤½¦¡ªº­È,ª½±µ±a¤JÀx¦s®æ

¦^´_ 3# kim223824

½Ð°Ý: §Ú·Q§âÃþ¦üªº¤èªkÀ³¥Î¨ì¥H¤U...
W.Sheets("¦h")ªºÄæ¼Æ= xcol
Sheets("·s")ªºA4 ¥H xcol¬°¨Ó·½,¥N¤J¤½¦¡,¦ý°õ¦æ³£µL§@¥Î,½Ð°Ý­n«ç»ò°µ??
  1.    Set Sh = W.Sheets("¦h")
  2.         Sh.Activate
  3.         i = "A2:E2"
  4.             xcol = Sh.Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
  5.         With W.Sheets("·s")
  6.         W.Sheets("·s").Activate
  7.         Range("A4" & xcol).Value = "=" & "¦h!A3*¦h!C3" '¤½¦¡
  8.        Range("A4" & xcol).Value = Range("A4" & xcol).Value
  9.         
  10.         End With   
½Æ»s¥N½X

TOP

¦^´_ 11# PJChen
¥[­Ó"."
   Set Sh = W.Sheets("¦h")
        Sh.Activate
        i = "A2:E2"
            xcol = Sh.Range(i).Columns.Count  '¬Ý´Xµ§¸ê®Æ
        With W.Sheets("·s")
        W.Sheets("·s").Activate
       .Range("A4" & xcol).Value = "=" & "¦h!A3*¦h!C3" '¤½¦¡
       .Range("A4" & xcol).Value = .Range("A4" & xcol).Value
        
        End With   
¥t¥~Range("A4" & xcol),¦pªGxcol=5,«h¬°Range("A45")
¦pªG¬O­nRange("A4")¥[¤WxcolªºÄæ¦ì«h§ï¬°
.Range("A4").Resize(1, xcol).Value = "=" & " ¦h!A3*¦h!C3" '¤½¦¡
.Range("A4").Resize(1, xcol).Value = .Range("A4").Resize(1, xcol).Value
¦pªGxcol=5,³o¼ËRange("A4")¦ÜRange("E4")³£·|©ñ¤J¤½¦¡,¦ý¤½¦¡ªº¦ì¸m·|ÅܤÆ
¦pªG­n¦V¤U©ñ´N±NResize(1,xcol)§ï¬°Resize(xcol,1)
¥H¤W´£¨Ñ°Ñ¦Ò

TOP

¦^´_ 12# jcchiang
±z¦n,
§Ú§â´ú¸ÕÀɮתþ¤W,¤è«KÀ°§Ú¬Ý¤@¤U¶Ü?¥¦¨ÌµM¤£¯à¶K¤W¸ê®Æ
¥H©¹copy¸ê®Æ³£¬O¥H¦C¼Æ¬°·Ç,¦ý²{¦b¦³·sªº»Ý¨D,¥HEX: A:XÄ欰«ü©w°Ï¶¡,¶K¸ê®Æor¦V¥k¶K¤½¦¡
W.Sheets("¦h")ªºÄæ¼Æ= xcol
Sheets("·s")ªºA4 ¥H xcol¬°¨Ó·½,¥N¤J¤½¦¡,¦ý°õ¦æ³£µL§@¥Î,½Ð°Ý­n«ç»ò°µ??
´`Äæ¶K¸ê®Æ.rar (22.02 KB)

TOP

¦^´_ 13# PJChen
¥i¥H°õ¦æªü!!
¤£¬O¦³¼g:Range("A4" & xcol),¦pªGxcol=5,«h¬°Range("A45")
¥H§Aªºµ{¦¡·|¦bSheets("·s")ªºRange("A45")¦³­Ó­È
¦pªG­n¦V¥k¶K¤½¦¡§ï¦¨³o¼Ë:
.Range("A4").Resize(1, xcol).Value = "=" & " ¦h!A3*¦h!C3" '¤½¦¡
.Range("A4").Resize(1, xcol).Value = .Range("A4").Resize(1, xcol).Value
¦ý¦]¬°¤½¦¡¨Ã¨S¦³±NÄæ¦ì©T©w,©Ò¥H¤½¦¡·|ÅÜ°Ê
Range("A4")= "=" & " ¦h!A3*¦h!C3" '¤½¦¡
Range("B4")= "=" & " ¦h!B3*¦h!D3" '¤½¦¡
Range("C4")= "=" & " ¦h!C3*¦h!E3" '¤½¦¡
¥H¦¹Ãþ±À

TOP

¦^´_ 14# jcchiang

·PÁ¦A¦¸«ü¾É,­ì¨Ó§Ú§âRange("A4" & xcol)²z¸Ñ¿ù»~,
³o­Ó.Range("A4").Resize(1, xcol).Value¤~¬O§Ú­nªºµ²ªG

TOP

¦^´_ 14# jcchiang

¤£¦n·N«ä,§Ú¦b¼g¤½¦¡¥N¤J®É,¦]¬°¤½¦¡«Üªø,¤@ª½¥X²{¬õ¦r,¬O§_»Ý­n´«¦æ?§Ú¸ÕµÛ´«¦æ,¦ý¤½¦¡«ç»ò¤Á³£¤£¦æ,½Ð±Ð³o»òªøªº¤½¦¡­n«ç»ò´«¦æ¤~¥i¥H?
  1.         With W.Sheets("Àu")
  2.             W.Sheets("Àu").Activate
  3.                 Range("B15:AI19").ClearContents
  4.                     i = "B15:AI15"
  5.                         xcol = W.Sheets("Àu").Range(i).Columns.Count
  6.                             .Range("B15").Resize(1, xcol).Value = "=" & "VLOOKUP(B$2,­¸¤ñ!$F:$FO,COUNTA(­¸¤ñ!$F$3:$FO$3),)"
  7.                             .Range("B15").Resize(1, xcol).Value = .Range("B15").Resize(1, xcol).Value
  8.                             .Range("B16").Resize(1, xcol).Value = "=" & "IF(B$2="","",SUMIF(­¸¤ñ!$F:$F,B$2,­¸¤ñ!$FT:$FT)+1)"
  9.                             .Range("B16").Resize(1, xcol).Value = .Range("B16").Resize(1, xcol).Value
  10.                             .Range("B17").Resize(1, xcol).Value = "=" & "IF(B14-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$B$4:$B$55))>=0,"",ABS(B14-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$B$4:$B$55))))"
  11.                             .Range("B17").Resize(1, xcol).Value = .Range("B17").Resize(1, xcol).Value
  12.                             .Range("B18").Resize(1, xcol).Value = "=" & "IF(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BJ$3:$CB$3="¦w")*(­¸¤ñ!$BJ$4:$CB$55))>=0,"OK",INT(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BJ$3:$CB$3="¦w")*(­¸¤ñ!$BJ$4:$CB$55))/SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))))"
  13.                             .Range("B18").Resize(1, xcol).Value = .Range("B18").Resize(1, xcol).Value
  14.                             .Range("B19").Resize(1, xcol).Value = "=" & "IF(B$14*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BI$4:$BI$55))>=0,"OK",INT(B$14*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BI$4:$BI$55))/SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))))"
  15.                             .Range("B19").Resize(1, xcol).Value = .Range("B19").Resize(1, xcol).Value
  16.         End With
½Æ»s¥N½X

TOP

.Range("B18").Resize(1, xcol) = "=IF(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))" & _
    "-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$BJ$3:$CB$3=""¦w"")*(­¸¤ñ!$BJ$4:$CB$55))>=0,""OK""," & _
    "INT(B$9*SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))-SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)" & _
    "*(­¸¤ñ!$BJ$3:$CB$3=""¦w"")*(­¸¤ñ!$BJ$4:$CB$55))/SUMPRODUCT((­¸¤ñ!$F$4:$F$55=B$2)*(­¸¤ñ!$G$4:$G$55))))"

¦r¦ê³sµ²¥Î & _
¤½¦¡¤¤¦³Âù¤Þ¸¹ªº"¦w", ¶·¥~¥[¤@¹ï""¦w""

TOP

¦^´_ 17# ­ã´£³¡ªL

ÁÂÁ­ã¤j,
¥[¤F¤Þ¸¹«á,´ú¸Õ¨S°ÝÃD¤F

TOP

¦^´_ 17# ­ã´£³¡ªL
­ã¤j,
§Ú±`¥Î¨ì¥H¤Uªº¥\¯à,¦ý¤u§@ªí¤ºªº¸ê®Æ«Ü¦h,FÄæ±`·|¶¡¹j´X¦C,¤S¦³¨ä¥L¸ê®Æ±µÄò,
³o®Éµ{¦¡´N·|§â¶¡¹j¦C¤]³£¶ñº¡
xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row  '¬ÝFÄæ¦ì´Xµ§¸ê®Æ
Range("JQ4:JQ" & xRow).value = "=AH4-BR4"
Range("JQ4:JQ" & xRow).value  = Range("JQ4:JQ" & xRow).value   'Âà´«­È
½Ð°Ý
­n«ç»òÅýµ{¦¡Range("JQ4:JQ" & xRow)¥HFÄ欰¨Ì¾Ú,¦ýFÄ榳ªÅ®æ®É,¤£­n©¹¤U°õ¦æ?(§Y ¶À©³Äæ¦ì¤£­n¦³¸ê®Æ)
20200213.221421.jpg
2020-2-13 22:14

TOP

¦^´_ 19# PJChen


Range("JQ4:JQ" & xRow).value = "=AH4-BR4"
§ï
Range("JQ4:JQ" & xRow) = "=IF(F4="""","""",AH4-BR4)"

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD