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

[µo°Ý] Dim arr1(1 To 100, 1 To 2),arr = [a2:d100],¨ä¤¤100,¦p¦ó¥ÎÅܼƨú¥N

[µo°Ý] Dim arr1(1 To 100, 1 To 2),arr = [a2:d100],¨ä¤¤100,¦p¦ó¥ÎÅܼƨú¥N

½Ð±Ð¦U¦ì«e½ú,Dim arr1(1 To 100, 1 To 2),arr = [a2:d100],¨ä¤¤100,­n¦p¦ó¥ÎÅܼƨú¥N,ÁÂÁÂ

±ø¥óÅã¥Ü.rar (15.93 KB)

§ù¤p¥­

Sub «ö¶s2_Click()
Dim i&, j&, k%
Dim arr As Variant
Dim MAS As Variant
MAS = Cells(Rows.Count, 1).End(xlUp).Row
'Dim arr1(1 To MAS, 1 To 2)
Dim arr1(1 To 100, 1 To 2)
arr = Range(Cells(2, 1), Cells(2, MAS))
'arr = ["A2:D" & MAS ]
For i = 1 To UBound(arr)
    If arr(i, 1) <> "" Then
        If arr(i, 1) = Cells(3, "e") And arr(i, 2) = Cells(3, "f") Then
            k = k + 1
            arr1(k, 1) = arr(i, 3)
            arr1(k, 2) = arr(i, 4)
        End If
    End If
Next i
Cells(3, "h").Resize(UBound(arr1), 2) = arr1
End Sub

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-2-20 04:28 ½s¿è

¦^´_ 1# dou10801

¦³ªÅÀ°§Ú¸Õ¸Õ¬Ý ¬O¤£¬O§A­nªºµ²ªG ÁÂÁÂ
  1. Public Sub ¤ñ¹ï½m²ß()
  2.     Application.ScreenUpdating = False

  3.     Range([H3], [I3].End(4)).ClearContents
  4.     Brr = Range([A1], [D65535].End(3))

  5.     For X = 2 To UBound(Brr)
  6.         If Brr(X, 1) & Brr(X, 2) = [E3] & [F3] Then
  7.             Brr(1 + k, 1) = Brr(X, 1)
  8.             Brr(1 + k, 2) = Brr(X, 2)
  9.             k = k + 1
  10.         End If
  11.     Next X
  12.    
  13.     [H3].Resize(k, 2) = Brr

  14.     Application.ScreenUpdating = True
  15. End Sub
½Æ»s¥N½X

TOP

·PÁ¨â¦ì«e½ú,§Úªº·N«ä¬ODim arr1(1 To 100, 1 To 2)¤¤ªº100¦p¦ó¥ÎÅܼƬ¡¥Î,¦]¬°
MAS = Cells(Rows.Count, 1).End(xlUp).Row
Dim arr1(1 To MAS, 1 To 2)
·|¿ù»~,[¥²»Ý¬O±`¼Æ¹Bºâ¦¡]
§ù¤p¥­

TOP

¦^´_ 4# dou10801


    Dim arr1()
mas = 999
ReDim arr1(1 To mas, 1 To 2)
Debug.Print UBound(arr1, 1), UBound(arr1, 2)

TOP

­è¾ÇVBA¦³«Ü¦h­n¾Ç²ß,·PÁ¦U¦ì«ü¾É,¸g½Õ¾ã«á¥H¤U¬O§Ú­nªº»yªk,ÁÂÁ¤j®a.
Dim i&, j&, k%
Dim arr As Variant
Dim MAS As Variant
MAS = Cells(Rows.Count, 1).End(xlUp).Row
'Dim arr1(1 To 100, 1 To 2)   '­ì½Z
ReDim arr1(1 To MAS, 1 To 2)  '½Õ¾ã«áªº»yªk
'arr = [a2:d100]    '­ì½Z
'arr = ["a2:d" & MAS ]
arr = Range(Cells(2, 1), Cells(MAS, 4).Address)   '½Õ¾ã«áªº»yªk
For i = 1 To UBound(arr)
    If arr(i, 1) <> "" Then
        If arr(i, 1) = Cells(3, "e") And arr(i, 2) = Cells(3, "f") Then
            k = k + 1
            arr1(k, 1) = arr(i, 3)
            arr1(k, 2) = arr(i, 4)
        End If
    End If
Next i
Cells(3, "h").Resize(UBound(arr1), 2) = arr1
§ù¤p¥­

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD