towster2
asked on
How to pass an array a subroutine Excel VBA
Hi,
I am trying to write a script that scans the contents of the first cell in the first 52 columns, then askes the user which columns they have data in that they would like merged. Then I would like to take their response and pass it to a subroutine to merge the cells appropriately. However, I am having trouble passing an array to my subroutine. I keep getting "Run-time error '1004': Application-defined or object defined error" Below is the snippet of code i am referring to. The error highlights the portion of code where i am merging 2 cells together. I think there is some sort of type mismatch, because when I change "Column" to a specific number...say 3, then the code runs without errors and merges the cells properly in column 3.
I am trying to write a script that scans the contents of the first cell in the first 52 columns, then askes the user which columns they have data in that they would like merged. Then I would like to take their response and pass it to a subroutine to merge the cells appropriately. However, I am having trouble passing an array to my subroutine. I keep getting "Run-time error '1004': Application-defined or object defined error" Below is the snippet of code i am referring to. The error highlights the portion of code where i am merging 2 cells together. I think there is some sort of type mismatch, because when I change "Column" to a specific number...say 3, then the code runs without errors and merges the cells properly in column 3.
Private Sub cmdOkay_Click()
Dim i As Long, msg As String, Check As String, boxa As String, boxb As String
Dim Arr(52, 1)
Dim j, k As Integer
j = 0
k = 0
'Generate a list of the selected items
With ListBox1
For i = 0 To .ListCount - 1
k = k + 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
Arr(j, 1) = k + 1
j = j + 1
End If
Next i
End With
...
...
If Check = vbYes Then
'CheckBox3
If UserForm1.CheckBox3.Value = True Then
Module4.Carrier_Merge Arr
End If
'Unload the userform since user is happy with selection(s)
Unload Me
Else
'User wants to try again, so clear listbox selections and
'return user to the userform
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End If
...
...
...
End Sub
Sub Carrier_Merge(ArrValues())
Dim i
Dim row1 As Integer
Dim row2 As Integer
Dim Column
Column = 0
row1 = 2
row2 = 3
Do Until IsEmpty(Range("A" & row1))
If Right(Range("A" & row1), 2) <> "_3" And Right(Range("A" & row2), 2) = "_3" Then
For i = 0 To UBound(ArrValues)
Column = ArrValues(i, 1)
Worksheets("Cell Site Demand").Range(Cells(row1, Column), Cells(row2, Column)).Merge
Next i
row1 = row1 + 2
row2 = row2 + 2
Else
row1 = row1 + 1
row2 = row2 + 1
End If
Loop
...
...
end sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. You were dead on!
Sub Carrier_Merge(ByVal ArrValues as Variant)
And just use it normally as you would an array.