[µo°Ý] °}¦C¤j¤p¦p¦ó¥HÅܼƴÀ¥N

jesscc

§Ú·Q¦bÀx¦s®æ¿é¤J¤@­Ó¼Æ­È¡A¨Ó¨M©w Br °}¦Cªº¤j¤p¡AµM«á§â°}¦Cªº¤¸¯À¦s¤J¦r¨å¸Ì¡A¦ý¬O¥X²{¤F"°}¦C¯Á¤Þ¶W¥X½d³ò"ªº´£¥Ü¡A½Ð°Ý¦U¦ì¤j¤j¬O­þ¸Ì¥X¿ù¤F?
Dim Br() As String,k%
k = [U1].Value
Set d = CreateObject("Scripting.Dictionary")
For Each A In Range([A9], [A6000].End(xlUp))
   ReDim Preserve Br(k)
    For j = 1 To k
      Br(j) = A.Offset(, j + 159).Value
    Next j
    d(A & "") = Br(k)
Next

­ã´£³¡ªL

ReDim Preserve Br(k)
§ï¦¨>> ReDim Br(1 To k)

jesscc

¦^´_ 2# ­ã´£³¡ªL
­ã¤j¡A§Ú¸Õ¤F¡AÁÙ¬O¦P¼Ë¥X²{°»¿ù¡C

¤U¸ü (17.7 KB)

2019-3-8 19:19

jackyq

try .....

d(A & "") = Br

jesscc

ª©¥DGBKEE
¦]¬°³oµ{¦¡¬O­n«ö³]©w¦nªº¸ô®|¶}±Ò¥t¤@­Ó¬¡­¶Ã¯¥h§ì¸ê®Æ¡A©Ò¥H½d¨ÒÀÉ«ÜÃø°µ¡A§Ú¦b·Q¬O¤£¬OÂà¸ê®Æªº³¡¤À¦³»~¡A§Ú¦A¶K¥XÂà¸ê®Æªº³¡¤À¦p¤U
    For Each A In .Range(.[F12], .[F10000].End(xlUp))
      If d.exists(A & "") Then
      A.Offset(, 15).Resize(, k) = d(A & "")
      Else: A.Offset(, 1).Value = "¬dµL¦¹ PN"
      End If
    Next

jesscc

¦^´_ 4# jackyq
j¤j¡A¦P¼Ë°»¿ù

jesscc

¶K¥X­ì©lµ{¦¡½X¡A¬¡­¶Ã¯¤Î¤u§@ªí¦WºÙ¦³²¤°µ­×§ï

1. Option Base 1
   
2. ¶}±Ò¥t¤@­Ó«D¤u§@¤¤¬¡­¶Ã¯WK(¥H¤U¬Ù²¤).
   
3. .
   
4. .
   
5. .
   
6. 
   
7. With Sheets("Inventory Report")
   
8. IRM1 = .[C4]
   
9. IRM2 = .[C5]
   
10. Set d = CreateObject("Scripting.Dictionary")
    
11. Set d1 = CreateObject("Scripting.Dictionary")
    
12. 
    
13. For Each A In .Range(.[A9], .[A6000].End(xlUp))
    
14. 
    
15. Dim Ar(13), Br(24)
    
16.     For i = 1 To 13
    
17.     Ar(i) = A.Offset(, i).Value
    
18.     Next i
    
19.     d(A & "") = Ar
    
20.    
    
21.     For j = 1 To 24
    
22.     Br(j) = A.Offset(, j + 159).Value
    
23.     Next j
    
24.     d1(A & "") = Br
    
25. 
    
26. Next
    
27. End With
    
28. 
    
29. Workbooks("L.xlsm").Activate
    
30. With Sheets("A INV Report")
    
31. .[E10,G10,G11,G12:AR3000].ClearContents
    
32. .[G10] = IRM1
    
33. .[G11] = IRM2
    
34. If .[H9] Like "* A *" Then: .[E10] = "A"
    
35. If .[H9] Like "* B *" Then: .[E10] = "B"
    
36. 
    
37.     For Each A In .Range(.[F12], .[F10000].End(xlUp))
    
38.       If d.exists(A & "") Then
    
39.       A.Offset(, 1).Resize(, 13) = d(A & "")
    
40.       End If
    
41.       If d1.exists(A & "") Then
    
42.       A.Offset(, 15).Resize(, 24) = d1(A & "")
    
43.       Else: A.Offset(, 1).Value = "¬dµL¦¹ PN"
    
44.       End If
    
45.     Next
    
46. End With
    
47. 
    
48. Erase Ar,Br
    
49. Set d = Nothing
    
50. Set d1 = Nothing
    
51. End Sub

½Æ»s¥N½X

jackyq

¤£¬O¥u¦³ Array
¥H¤U¤]·|²£¥Í "°}¦C¯Á¤Þ¶W¥X½d³ò"

Workbooks("¤£¦s¦bªºwb").Activate
Sheets("¤£¦s¦bªºws").Select

jesscc

¦^´_ 8# jackyq
­ì©lµ{¦¡°õ¦æ¨S¦³°ÝÃD¡A§Ú¥u¬O·Q§â­ì¥»ªºBr°}¦C(¤j¤p©T©w¬°24­Ó¤¸¯À)§ï¦¨¥i¨ÌÀx¦s®æ¿é¤Jªº¼Æ­ÈÀH·NÅܰʤj¤p

jackyq

' ¦³¥i¯à§Aªº k­È ¥X²{ <=0
' §A¥i¥H¸Õ¸Õ¤U­±

Option Base 1  

Sub QX9650_____()

Dim a()


k = 10
ReDim a(k)

k = 1
ReDim a(k)

k = 0
ReDim a(k)  '§A§â°}¦C¤U­­³]¬° 1 , µ²ªGÅܦ¨ Redim  a(1 to 0)

End Sub




'    If k >= 1 Then
'        For Each a In .Range(.[A9], .[A6000].End(xlUp))
'            ReDim Br(k)
'
'            For j = 1 To k
'            Br(j) = a.Offset(, j + 159).Value
'            Next j
'            d1(a & "") = Br
'        Next
'    End If