Andy2483

¦^´_ 1# adam2010


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
¤½¦¡Â²¤Æ 20231002.zip (115.5 KB)

¤U¸ü¦¸¼Æ: 1

2023-10-2 09:58

°õ¦æµ²ªG:

¤U¸ü (219.64 KB)

2023-10-2 09:58

Option Explicit
Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function
Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range(Sheets("»¡©ú").Cells(ActiveSheet.UsedRange.Rows.Count, "A"), [»¡©ú!IV1].End(xlToLeft))
For j = 1 To UBound(Brr, 2)
   If Trim(Brr(1, j)) <> "" Then Z("/" & Trim(Brr(1, j)) & "/") = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists("/" & Crr(i, 1) & "/") <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Crr(i, 1) & "/")) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/W/")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Split(Crr(i, 2), "X")(0) & """/")) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

Andy2483

¦^´_ 2# Andy2483
¦^´_ 1# adam2010


    ­×¥¿¦Û¤vªº²Ê¤ß¤j·N,½Ð¥H¦p¤U½d¨Ò´ú¸Õ
¤½¦¡Â²¤Æ 20231002-1.zip (115.58 KB)

¤U¸ü¦¸¼Æ: 2

2023-10-2 10:33

Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
For j = 1 To UBound(Brr, 2)
   If Trim(Brr(1, j)) <> "" Then Z("/" & Trim(Brr(1, j)) & "/") = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists("/" & Crr(i, 1) & "/") <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Crr(i, 1) & "/")) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/W/")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Split(Crr(i, 2), "X")(0) & """/")) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

Andy2483

¦^´_ 4# adam2010


    ÁÂÁ«e½ú¦^´_
¥H¤U¬O §PÂ_¶¶§Ç 1.¯S®í®Æ¸¹«e6½X ¡÷ 2.®Æ¸¹²Ä7½X=W ¡÷ 3.§å¦¸½X(6 §ä 6"Äæ ,8 §ä 8"Äæ ,12 §ä 12"Äæ )

Option Explicit
Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
For j = 1 To UBound(Brr, 2)
   If Left(Trim(Brr(1, j)), 6) <> "" Then Z(Left(Trim(Brr(1, j)), 6)) = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists(Left(Trim(Crr(i, 1)), 6)) <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Trim(Crr(i, 1)), 6))) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("W")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Trim(Split(Crr(i, 2), "X")(0) & """"))) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

Andy2483

¦^´_ 5# Andy2483


    ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú,¥H¤U¬O½Æ²ßªº¤ß±oµù¸Ñ

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
'¡ô«Å§iÅܼÆ:(Brr,Crr,Z)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼Æ,(j,V,V7)¬Oµu¾ã¼Æ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
'¡ôÃö©ó "»¡©ú"¤u§@ªíªºµ{§Ç:
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H²Ä1¦C³Ì¥ªÃ䦳¤º®eÀx¦s®æ¨ì AÄæ¤w¨Ï¥Î¦C(³Ì¤j¦C¸¹)Àx¦s®æ,
'¥H³o½d³òÀx¦s®æ­È±a¤JBrr°}¦C¤¤
For j = 1 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!¥OjÅܼƱq1 ¨ìBrr°}¦C³Ì¤j¯Á¤ÞÄ渹
   If Left(Trim(Brr(1, j)), 6) <> "" Then Z(Left(Trim(Brr(1, j)), 6)) = j
   '¡ô¦pªG²Ä1¦Cj°j°éÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê¤£¬OªÅ¦r¤¸!
   '´N¥O¥H ²Ä1¦Cj°j°éÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê·íkey,
   'jÅܼƷíitem¯Ç¤JZ¦r¨å¤¤
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
'¡ô¥OCrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H[O1]Àx¦s®æ¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ,
'¥H³o½d³òÀx¦s®æ­È±a¤JCrr°}¦C¤¤
For i = 2 To UBound(Crr)
'¡ô³]¶¶°j°é!¥OiÅܼƱq2 ¨ìBrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   '¡ô¥OV³oµu¾ã¼ÆÅܼƬO i°j°é¦C15ÄæCrr°}¦C­ÈÂà¤Æªº¼Æ­È
   '¡ô¥OV7³oµu¾ã¼ÆÅܼƬO i°j°é¦C7ÄæCrr°}¦C­ÈÂà¤Æªº¼Æ­È
   '¡ô¥O(i-1)°j°é¦C1ÄæCrr°}¦C­È¬OªÅ¦r¤¸
   If Z.Exists(Left(Trim(Crr(i, 1)), 6)) <> Empty Then
   '¡ô¦pªG¥H²Ä1¦Cj°j°éÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê(¥H¤UºÙ²£«~½s¸¹),
   '­Y¥H²£«~½s¸¹¬d¨ìZ¦r¨å¦³¦¹key!
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Trim(Crr(i, 1)), 6))) * V, 0)
      '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,²£«~½s¸¹¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
      '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
      ElseIf Right(Left(Trim(Crr(i, 1)), 7), 1) = "W" Then
      '¡ô§_«h¦pªG¥H ²Ä1¦Cj°j°éÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú²Ä7¦r¤¸¬O"W"!
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("W")) * V, 0)
        '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,"W"¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
        '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Trim(Split(Crr(i, 2), "X")(0) & """"))) * V, 0)
         '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,§å¸¹²Ä1³W®æ½X¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
         '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
         'µù §å¸¹²Ä1³W®æ½X: i°j°é¦C²Ä2ÄæCrr°}¦C­È,¥H"X"¤À³Î¦¨ªº¤@ºû°}¦Cªº²Ä0¯Á¤Þ¸¹°}¦C­È ³s±µ "²Å¸¹
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
'¡ô¥O"WIP"¤u§@ªí[D2]¦V¤UÂX®iCrrÁa¦V³Ì¤j¯Á¤Þ¦C¸¹-1¦C,¦¹½d³òÀx¦s®æ­È¥HCrr°}¦C­È±a¤J
Set Z = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub