¤p§Ì¦³¤@ӱƵ{ªº¥¨¶°¡A¤§«e¤]¬O½Ð±Ð³o¸Ìªº°ª¤âstillfish00¨ó§U¸Ñ¨Mªº
¤£ª¾¹D¬O¤£¬O¹q¸£¦ÑÂÂÁÙ¬O¸ê®Æ¶q¹L¤j¡A¨Ï¥Î¤@¬q®É¶¡¤§«á³o¤@¬q·U¶]·UºC ( ¥Î¤½¥qªºNB¶]±Nªñ6~7¤ÀÄÁ )
·Q½Ð±Ð¦U¦ì°ª¤â¬O§_¦³¥[³tªº¤èªk¡A©ÎªÌ¨ä¹ê¬O¦]¬°¨ä¥L³¡¤À§Ú¦Û¦æ¿ý»s«÷´ê¥X¨Óªº¬q¸¨¼vÅT©Ò¥H¤~·|·U¨Ó·UºC?
ESOD_XT_AA.rar (472.05 KB)
Dim s1 As Long, s2 As Long
Dim cindex As Long
With Sheets("¥X³f¤é")
Set Rng = .Range(.[a1], .[a1].End(xlToRight).End(xlDown).Offset(, -1)) '[¥X³f¤é]¸ê®Æ½d³ò
End With
For Each c In Sheets("¥æ´Á").Range("E2:E" & Sheets("¥æ´Á").[a1].End(xlDown).Row) '[¥æ´Á]¸ê®Æ¶ñ¤J½d³ò
If c.Offset(, -4).Value <> c.Offset(-1, -4).Value Then
cindex = 1
s1 = 0 '²Ö¿n¦Ü«e¤@§å¼Æ¶q
s2 = 0 '²Ö¿n¥X³f»Ý¨D¼Æ¶q
Else
s1 = s1 + c.Offset(-1, -1).Value
End If
Do While s2 <= s1
cindex = cindex + 1
If Application.IsError(Application.VLookup(c.Offset(, -4).Value, Rng, cindex, False)) Then
Exit Do
Else
s2 = s2 + Application.VLookup(c.Offset(, -4).Value, Rng, cindex, False)
End If
Loop
If s2 <= s1 Then
c.Value = "NA"
Else
c.Value = Rng.Cells(1, cindex).Value
End If
Next
Set Rng = Nothing
'¦bEÄæ¶ñ¤J¥æ´Á |