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

dou10801

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

acdx

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

°a¤ªºµ

¦^´_ 1# dou10801

¦³ªÅÀ°§Ú¸Õ¸Õ¬Ý ¬O¤£¬O§A­nªºµ²ªG ÁÂÁÂ

1. Public Sub ¤ñ¹ï½m²ß()
   
2.     Application.ScreenUpdating = False
   
3. 
   
4.     Range([H3], [I3].End(4)).ClearContents
   
5.     Brr = Range([A1], [D65535].End(3))
   
6. 
   
7.     For X = 2 To UBound(Brr)
   
8.         If Brr(X, 1) & Brr(X, 2) = [E3] & [F3] Then
   
9.             Brr(1 + k, 1) = Brr(X, 1)
   
10.             Brr(1 + k, 2) = Brr(X, 2)
    
11.             k = k + 1
    
12.         End If
    
13.     Next X
    
14.    
    
15.     [H3].Resize(k, 2) = Brr
    
16. 
    
17.     Application.ScreenUpdating = True
    
18. End Sub

½Æ»s¥N½X

dou10801

·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ºâ¦¡]

quickfixer

¦^´_ 4# dou10801


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

dou10801

­è¾Ç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