[µo°Ý] ½Ð°Ý¦³Ãö¹ï·Ó±a¤J¡A§ä´M«Ü¤[¤£ª¾¹D«ç»ò°µ¡A·P®¦

doulioufire

¦p¹Ï¤@©Òªí¥Ü¡A¹ï·Ó¤é´Á¡A±N¦WºÙ¶ñ¤Jªí³æ¡A
¦p¹Ï¤G©Òªí¥Ü¡A¹ï·Ó¤é´Á¡A±N¦WºÙ¶ñ¤Jªí³æ¡A¦ý¬O¤£¥i¥H¥²¶·«O¯d01¤éªº¸ê®Æ

¤£ª¾¹D³o¼Ëªí¹F¡A²M·¡¶Ü¡H·PÁ¦U¦ì¡I

VBA©Î¨ç¼Æ³£¥i¥Hªº¡A¦A¦¸·PÁ¡I

Scott090

¦^´_ 1# doulioufire


³o­Ó¬O§_²Å¦X»Ý¨D?

1. Option Base 1
   
2. Sub Test()
   
3.     Dim aa, bb
   
4.     Dim i&, j%
   
5.     aa = Range("A2:B" & [b2].End(xlDown).Row).Value
   
6.     ReDim bb(1, UBound(aa))
   
7.     For i = 1 To UBound(aa)
   
8.        If aa(i, 1) <> "" Then
   
9.            j = j + 1                             '­pºâ¦³´X­Ó¥N¸¹
   
10.            bb(1, aa(i, 1)) = aa(i, 2)  '¨Ì¥N¸¹±Æ§Ç
    
11.        End If
    
12.     Next
    
13.     Cells(Cells(3, "U") + 1, "D").Resize(, j) = bb   '©ñ¦b¤éªº¹ïÀ³¦C
    
14. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¤½¦¡·fVBA¡G
Sub TEST()
With [D1:S1].Offset([U3], 0)
¡@¡@.Formula = "=IF(ISNA(MATCH(D$1,$A:$A,)),"""",VLOOKUP(D$1,$A:$B,2,))"
¡@¡@.Value = .Value
End With
End Sub

­n«O¯d«e­È¡A¶È¥Î¤½¦¡®É¡A¥²¶·¤â°Ê¶K¦¨¡e­È¡f¡A¨Ï¥ÎVBA¥i¤@®ð¨þ¦¨¡I
¡@

­ã´£³¡ªL

§¹¾ã¨Ç¡AÁקK¾Þ§@¿ù»~±N¤§«eªº¸ê®ÆÂл\¤F¡G
¡@
Sub TEST()
If Val([U3]) <= 0 Then Exit Sub
With [D1:S1].Offset([U3], 0)
¡@¡@¡@If Application.CountA(.Cells) > 0 Then
¡@¡@¡@¡@¡@If MsgBox("«ü©w¤é´Á¤w¦³¸ê®Æ¡A¬O§_§ó·s¼Æ¾Ú¡H¡@", 0 + 4 + 256) = vbNo Then Exit Sub
¡@¡@¡@End If
¡@¡@¡@.Formula = "=IF(ISNA(MATCH(D$1,$A:$A,)),"""",VLOOKUP(D$1,$A:$B,2,))"
¡@¡@¡@.Value = .Value
End With
End Sub

doulioufire

·PÁ ·PÁ ­ã´£³¡ªL Scott090  §Ú¦A¨Ó¸ÕÅç¬Ý¬Ý