[發問] Dim arr1(1 To 100, 1 To 2),arr = [a2:d100],其中100,如何用變數取代

dou10801

請教各位前輩,Dim arr1(1 To 100, 1 To 2),arr = [a2:d100],其中100,要如何用變數取代,謝謝

acdx

Sub 按鈕2_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

軒云熊

回復 1# dou10801

有空幫我試試看 是不是你要的結果 謝謝

1. Public Sub 比對練習()
   
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

複製代碼

dou10801

感謝兩位前輩,我的意思是Dim arr1(1 To 100, 1 To 2)中的100如何用變數活用,因為
MAS = Cells(Rows.Count, 1).End(xlUp).Row
Dim arr1(1 To MAS, 1 To 2)
會錯誤,[必需是常數運算式]

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有很多要學習,感謝各位指導,經調整後以下是我要的語法,謝謝大家.
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)   '原稿
ReDim arr1(1 To MAS, 1 To 2)  '調整後的語法
'arr = [a2:d100]    '原稿
'arr = ["a2:d" & MAS ]
arr = Range(Cells(2, 1), Cells(MAS, 4).Address)   '調整後的語法
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