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

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

¥»©«³Ì«á¥Ñ samwang ©ó 2021-9-25 16:16 ½s¿è

¦^´_ 9# PJChen


¤£¦n·N«ä¡A½Ð±Ð¤@¤U±z¬JµM­n¥ÎVBA¬°¤°»òÁÙ¦bExcel¼g¤½¦¡?
«á¾Çı±o¥ÎVBA¦bexcel¼g¤½¦¡©Ç©Çªº¡A
ª½±µ¦bexcel¼g¤J¤½¦¡¡A³o¼Ë¤£´N¦n¤F¶Ü?

TOP

¦^´_ 3# PJChen


¦P¤@­ÓÀÉ®×
CÄæ"¨ÑÀ³°Ó"¤À¬°¤j & ¬ü
±q²Ä3¦C¶}©l,CÄæ<>¬ü,«h¾ã¦C§R°£
³o¬O­n³æ¿Wªºµ{¦¡....
>> ½Ð°Ý#7¡B#8¥i¥H¥Î¶Ü?

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-9-25 21:11 ½s¿è

¦^´_ 11# samwang
±z¬O«üK3=K2+G3-F3-H3-I3+J3ªº¤½¦¡¶Ü?
³o¬O¬°¤FÅý¦^ÂЪ̪¾¹D­n¦p¦ó­pºâ
¦pªG¬O«ü³o­Óªº¸Ü,¦]¬°§Ú¤£·|¨ä¥L¼gªk,¥Î¤F¤@¬q®É¶¡«á,¤S­n·Q¾Ç¨ä¥L¤è¦¡,
For Each xR In Range([B3], [b65535].End(3))
    If xR >= d Then 'k+g-f-h-i+j
        xR.Offset(, 9) = xR.Offset(-1, 9) + xR.Offset(, 5) - xR.Offset(, 4) - xR.Offset(, 6) - xR.Offset(, 7) + xR.Offset(, 8)
    End If
Next

§Ú³Ì·Q¾Çªº¬O°}¦C¤è¦¡,©Ò¥H¨D±Ð·|¼gªº¤H
§Ú±`¬Ý­ã¤jªºµ{¦¡,¦ý§Ú¸ê½è¤£¦n,¤@ª½¨S¾Ç·|,
²{¦b³o­ÓÀÉ,¨C¤@Äæ­pºâ,§Ú³£¨Ï¥Î³æ¿Wªºµ{¦¡,

¹³«e­±ªº°ÝÃD
AÄæ,­n¨úBÄ檺¦~¤ë,YYYY..M
§Ú­ì¥ý¤]¬O³æ¿Wªº¼gªk,¦ý§Ú¨S·Q¨ì§A§â¤G­Ó»Ý¨D¨Ö¦b¤@°_¤F

§Ú·Q§â³o­ÓÀÉ,¨C­Ó»Ý­n­pºâªºÄæ¦ì,³£Âà´«¬°°}¦C¼gªk,
¦]¬°¥u¼g¤@Ä檺­pºâ¤ñ¸û¨S¨º»ò½ÆÂø,
§Æ±æ¬Ý§O¤Hªºµ{¦¡«á,¥i¥H¾Ç±o¤@©Û¥b¦¡¡I

§Ú¦A¦Û¦æ­×§ï,¹J¨ì§ï¤£¥X¨Ó¦A´£°Ý,
§Ú¤£¤Ó²M·¡¬O§_¨CºØ»Ý¨D³£¥i¥H¥Î°}¦C...
³o¥u¬O§Ú¤ß¸Ì·Qªº¡I

TOP

¦^´_ 12# samwang

³o¤G­Óµ{¦¡°õ¦æ«á
³£¥¿½T¡I

TOP

¦^´_ 10# samwang

#6
  .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
³o¬qµ{¦¡,³£¥u¯à±q²Ä3¦C©¹¤U¼g

#10
  .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
³o¬qµ{¦¡,µLªkºI¨úBÄæ¸ê®Æ,±NYYYY..M¶ñ¤JAÄæ

TOP

¦^´_ 15# PJChen

#10
  .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
³o¬qµ{¦¡,µLªkºI¨úBÄæ¸ê®Æ,±NYYYY..M¶ñ¤JAÄæ
>> With [a3].Resize(R - 2)
    .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
    End With
    .Value = .Value 'Âର­È
³o¥\¯à¥¿±`¡A³o¬O±NBÄæ¸ê®Æ¨S¸g¹L±ø¥óÂର "2021..6 "

¦p¹L±zªº»Ý¨D¬O­n¸g¹L¤é´Á¤ñ¸û¡A½Ð¦Û¦æ±N .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
²¾¨ìif ©³¤U
Sub test()
Dim Arr, d, R%
d = Sheets("VBA").[Af2]
R = [¥_°Ï!b65536].End(3).Row
With Sheets("¥_°Ï").Range("a1:k" & R)
'    With [a3].Resize(R - 2)
'    .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
'    End With
'    .Value = .Value 'Âର­È
    Arr = .Value
    For i = 3 To UBound(Arr)
        If Arr(i, 2) >= d Then
            .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
            .Cells(i, "k").Formula = "=k" & i - 1 & "+G" & i & "-F" & i & "-H" & i & "-I" & i & "+j" & i
        End If
    Next
    '.Value = .Value 'Âର­È
End With
End Sub
½Ð¦b´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-9-26 06:39 ½s¿è

¦^´_ 15# PJChen

#10
  .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
³o¬qµ{¦¡,µLªkºI¨úBÄæ¸ê®Æ,±NYYYY..M¶ñ¤JAÄæ
>> With [a3].Resize(R - 2)
    .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
    End With
    .Value = .Value 'Âର­È
³o¥\¯à¥¿±`¡A³o¬O±NBÄæ¸ê®Æ¨S¸g¹L±ø¥óÂର "2021..6 "


¦p¹L±zªº»Ý¨D¬O­n¸g¹L¤é´Á¤ñ¸û¡A½Ð¦Û¦æ±N .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
²¾¨ìif ©³¤U¦p¤U
Sub test()
Dim Arr, d, R%
d = Sheets("VBA").[Af2]
R = [¥_°Ï!b65536].End(3).Row
With Sheets("¥_°Ï").Range("a1:k" & R)
'    With [a3].Resize(R - 2)
'    .Formula = "=YEAR(B3) & "".."" & MONTH(B3)"
'    End With
'    .Value = .Value 'Âର­È
    Arr = .Value
    For i = 3 To UBound(Arr)
        If Arr(i, 2) >= d Then
            .Cells(i, "a").Formula = "=YEAR(RC[1]) & "".."" & MONTH(RC[1])"
            .Cells(i, "k").Formula = "=k" & i - 1 & "+G" & i & "-F" & i & "-H" & i & "-I" & i & "+j" & i
        End If
    Next
    '.Value = .Value 'Âର­È
End With
End Sub
½Ð¦b´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

TOP

¦^´_ 17# samwang
´ú¸Õ¨S°ÝÃD¤F¡I

¦A½Ð°Ý¦P¤@ÀÉ®×,¥t¤@¬qµ{¦¡ ®Ö¾P©ú²Ó2021.rar (28.77 KB)
EÄæ­n¶ñ¤J³æ¸¹(³sµ²)T&S&R
¦ýRÄæ=""®É,«hEÄæ="µL¥æ³f"
½Ð°Ý­n¦p¦ó§ï³o¬qµ{¦¡?
        If Arr(i, 2) >= d And Arr(i, "r") = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
            .Cells(i, "e").Formula = "µL¥æ³f"
        Else
            .Cells(i, "e").Formula = "=T&S&R"
        End If
    Next

TOP

¦^´_ 17# samwang

¥t¥~¤@­ÓUÄæµ{¦¡,¬õ¦r³o¤@¬q,¤é´Á+1ªº¼gªk¤£¯à¥Î,½Ð°Ý¦p¦óÅý¤é´Á+1
¨Ò¦p:u3=8/3,«h+1=8/4
    For i = 3 To UBound(Arr)
        If Arr(i, 2) >= d And Arr(i, 4) <> "¤¤©M" Or Arr(i, 4) <> "¤º´ò" And Arr(i, 4) <> "¦Á¤î" Then 'i=¦C
            .Cells(i, "u").Formula = "=Arr(i, 2)" + 1

        ElseIf Arr(i, 2) >= d And Arr(i, 4) = "¤¤©M" Or Arr(i, 4) = "¤º´ò" And Arr(i, 4) = "¦Á¤î" Then
            .Cells(i, "u").Formula = "Arr(i, 2)"
        End If
    Next

TOP

¦^´_ 18# PJChen

EÄæ­n¶ñ¤J³æ¸¹(³sµ²)T&S&R
¦ýRÄæ=""®É,«hEÄæ="µL¥æ³f"
½Ð°Ý­n¦p¦ó§ï³o¬qµ{¦¡?
>> ¨Ì¾Ú±zªºµ{¦¡°µ­×§ï¦p¤U
        If Arr(i, 2) >= d And Arr(i, 18) = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
            .Cells(i, "e") = "µL¥æ³f"
        Else
             .Cells(i, "e").Formula = "=T" & i & " & S" & i & "& R" & i & ""
        End If


¦ý¬O¨Ì¾Ú±zªº´y­z¡A«á¾Çı±o±ø¥ó¥i¥H©î¦¨2¬qif ¤ñ¸û¦n¡AÁÂÁÂ
  If Arr(i, 2) >= d And Arr(i, 18) = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
          .Cells(i, "e") = "µL¥æ³f"
end if
If Arr(i, 2) >= d And Arr(i, 18) <> "" Then
        .Cells(i, "e").Formula = "=T" & i & " & S" & i & "& R" & i & ""
end if

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD