Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,®}®§¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:

¤U¸ü (82.15 KB)

2023-5-16 11:35

°õ¦æµ²ªG:

¤U¸ü (83.46 KB)

2023-5-16 11:35

Option Explicit
Sub TEST() '¡ô
Dim Brr, Y, i&, T$, X&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = [A1].CurrentRegion
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 2)
   '¡ô¥OTÅܼƬOBrr°}¦C²Ä2Ä檺¦r¦ê­È
   If Right(T, 4) Like "#PCS" Then
   '¡ô¦pªGTÅܼƪº¥k°¼4¦r²Å¦X±ø¥ó?
      X = StrReverse(Val("1" & Mid(StrReverse(T), 4))) \ 10
      '¡ô¥OXÅܼƬO ±NTÅܼƤÏÂà¤Æ¬°¼Æ­È¦A¤ÏÂà¦^¨Óªº¼Æ­È
      Y(X) = "B" & i
      '¡ô¥O¥HXÅܼƷíkey,item¬O "B"¦r¥À±µiÅܼÆ,¯Ç¤JY¦r¨å¸Ì
   End If
Next
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 1)
   '¡ô¥OTÅܼƬOBrr°}¦C²Ä1Ä檺¦r¦ê­È
   If Right(T, 4) Like "#PCS" Then
   '¡ô¦pªGTÅܼƪº¥k°¼4¦r²Å¦X±ø¥ó?
      X = StrReverse(Val("1" & Mid(StrReverse(T), 4))) \ 10
      '¡ô¥OXÅܼƬO ±NTÅܼƤÏÂà¤Æ¬°¼Æ­È¦A¤ÏÂà¦^¨Óªº¼Æ­È
      Brr(i - 1, 1) = "PCS¼Æ¦P_" & Y(X)
      '¡ô¥OBrr°}¦C²Ä1Äæ­È¬O ¥HXÅܼƬdY¦r¨å±o¨ìitem­È
      Else
      Brr(i - 1, 1) = "µL"
      '¡ô§_«h¥OBrr°}¦C²Ä1Äæ­È¬O "µL"
   End If
Next
[C2].Resize(UBound(Brr) - 1, 1) = Brr
'¡ô¥OBrr°}¦C²Ä1Äæ­È±q[C2]Àx¦s®æ¶}©l­Ë¤J
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub