ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2019/3/8 10:56): ¬Ý§Aªºµ{¦¡½X¨S°ÝÃD,¥iªþÀÉÅý¤j®a¬Ý¬Ý

§Ú·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
Jess

¦^´_ 5# jesscc

§Ú¦b·Q¬O¤£¬OÂà¸ê®Æªº³¡¤À¦³»~

¦Û¦æ°»¿ù¬Ý¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Br() As String, k%, D As Object, A As Range, J As Integer
  4.     On Error GoTo RR   'µ{¦¡¦³¿ù»~®É¨ì«ü©w¦æ¸¹(RR)
  5.     k = [U1].Value
  6.     Set D = CreateObject("Scripting.Dictionary")
  7.     For Each A In Range([A9], [A6000].End(xlUp))
  8.         ReDim Preserve Br(k)
  9.         For J = 1 To k
  10.             Br(J) = A.Offset(, J + 159).Value
  11.         Next J
  12.         D(A & "") = Br(k)
  13.     Next
  14.     Exit Sub
  15. RR:
  16.    Application.VBE.Windows("°Ï°ìÅܼÆ").Visible = True
  17.    
  18.    Stop
  19.    'µ{¦¡¼È°± ¬d¬Ý©Ò¦³ÅܼƬݬݿù»~¦b­þ¸Ì
  20. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2019-3-8 23:19 ½s¿è

(1)
k=0 ®É
·|Åܦ¨ Redim Br(1 To 0)  '--> VBA ¨S¦³¿ìªk°t¸m³oºØ°}¦C
IF K>=1 Then ..... End if ¥i¥H¨¾¤î °õ¦æ¨ì Redim Br(1 To 0)  

(2)
¾¨¶q¤£­n¥h¨Ï¥Î Option Base 1 ³oºØªF¦è

³Ì¦nª½±µ¼g¦¨ Redim Br(1 To K)

TOP

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

¤£¦n·N«ä¡Aj¤j¡C´ú¸Õ¤wOK¡A¬O§Ú¤Ö­×§ï¤F¤@­Ó¦a¤è¡C¤£¹L§ÚÁÙ¬O¤£¤Ó©ú¥Õ¬°¤°»ò¥[¤F³o¬q
If k >= 1 Then
.
.

End If
´N¨S°ÝÃD¤F
Jess

TOP

' ¦³¥i¯à§Aªº k­È ¥X²{
jackyq µoªí©ó 2019-3-8 21:54

ÁöµM¨S¦³¥X²{°»¿ù¡A¦ý¬O¬d¸ßµ²ªGÅܦ¨³o¼Ë
Â^¨ú.PNG
2019-3-8 22:34
Jess

TOP

§Ú­×§ïµ{¦¡®É¤@¶}©l´N§âU1ªº­È«ü©wµ¹k¤F-->  k = [U1].Value¡A¦ÓU1ªº­È½T©w¬O¤j©óµ¥©ó1ªº¡C(¹ê»Ú¤u§@®ÉU1ªº¿é¤J­È¥²©w¬O¤j©óµ¥©ó24)
Jess

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2019-3-8 22:03 ½s¿è

' ¦³¥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

TOP

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

TOP

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

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

TOP

¶K¥X­ì©lµ{¦¡½X¡A¬¡­¶Ã¯¤Î¤u§@ªí¦WºÙ¦³²¤°µ­×§ï
  1. Option Base 1
  2. ¶}±Ò¥t¤@­Ó«D¤u§@¤¤¬¡­¶Ã¯WK(¥H¤U¬Ù²¤).
  3. .
  4. .
  5. .

  6. With Sheets("Inventory Report")
  7. IRM1 = .[C4]
  8. IRM2 = .[C5]
  9. Set d = CreateObject("Scripting.Dictionary")
  10. Set d1 = CreateObject("Scripting.Dictionary")

  11. For Each A In .Range(.[A9], .[A6000].End(xlUp))

  12. Dim Ar(13), Br(24)
  13.     For i = 1 To 13
  14.     Ar(i) = A.Offset(, i).Value
  15.     Next i
  16.     d(A & "") = Ar
  17.    
  18.     For j = 1 To 24
  19.     Br(j) = A.Offset(, j + 159).Value
  20.     Next j
  21.     d1(A & "") = Br

  22. Next
  23. End With

  24. Workbooks("L.xlsm").Activate
  25. With Sheets("A INV Report")
  26. .[E10,G10,G11,G12:AR3000].ClearContents
  27. .[G10] = IRM1
  28. .[G11] = IRM2
  29. If .[H9] Like "* A *" Then: .[E10] = "A"
  30. If .[H9] Like "* B *" Then: .[E10] = "B"

  31.     For Each A In .Range(.[F12], .[F10000].End(xlUp))
  32.       If d.exists(A & "") Then
  33.       A.Offset(, 1).Resize(, 13) = d(A & "")
  34.       End If
  35.       If d1.exists(A & "") Then
  36.       A.Offset(, 15).Resize(, 24) = d1(A & "")
  37.       Else: A.Offset(, 1).Value = "¬dµL¦¹ PN"
  38.       End If
  39.     Next
  40. End With

  41. Erase Ar,Br
  42. Set d = Nothing
  43. Set d1 = Nothing
  44. End Sub
½Æ»s¥N½X
Jess

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD