CODEWITHSUNDEEP
excelvba
EndlessLoop
EndlessLoop

Reputation: 444

VBA concatenated arrays comparison - error 1004 coming up

I have these entries in rows 1 to 10 and columns A to D:

a   10  a   10
b   20  b   20
c   30  c   30
d   40  d   40
e   50  e   50
f   60  f   60
g   70      
h   80      
i   90      
j   100

With VBA code (that I will paste below), I am wanting to, using VBA arrays, build a concatenated "virtual column" of A and B and C and D. Then, I am wanting to create a third array that is populated with an array that gives me a MATCH result for each array element using the concatenation of A and B compared to the same of C and D. (There's a point to all this: it's the prototype of something more complex I want to build later on.)

It works up until the 6th element in the array, but beyond that it gives me the 1004 error (with element values after that Empty in the Watch window).

Can anyone give me some pointers why?

Here is the (probably inelegant) VBA code:

Sub concatarray()

Dim count1 As Integer
Dim count2 As Integer
count1 = Application.CountA(Columns("A:A"))
count2 = Application.CountA(Columns("C:C"))
Dim Column1() As Variant
Dim Column2() As Variant
Dim virtColumn3() As Variant
Dim Column4() As Variant
Dim Column5() As Variant
Dim virtColumn6() As Variant
ReDim virtColumn3(1 To count1, 1 To 1)
ReDim virtColumn6(1 To count2, 1 To 1)
Column1 = Range("A1:A" & count1)
Column2 = Range("B1:B" & count1)
Column4 = Range("E1:E" & count2)
Column5 = Range("F1:F" & count2)
For i = 1 To count1 
    virtColumn3(i, 1) = Column1(i, 1) & Column2(i, 1)
Next i
For i = 1 To count2
    virtColumn6(i, 1) = Column4(i, 1) & Column5(i, 1)
Next i
Dim virtColumn7() As Variant
ReDim virtColumn7(1 To count1, 1 To 1)
For i = 1 To count1
    virtColumn7(i, 1) = WorksheetFunction.Match(virtColumn3(i, 1), virtColumn6, 0)
Next i

End Sub

It's almost definitely this snippet that's wrong:

 For i = 1 To count1
        virtColumn7(i, 1) = WorksheetFunction.Match(virtColumn3(i, 1), virtColumn6, 0)
 Next i

Thanks in advance.

Upvotes: 0

Views: 99

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

WorksheetFunction.Match() throws an error if a match is not found.

Instead of trapping that error, you can use the Application.Match() version, which returns an error:

Dim v 'variant  
For i = 1 To count1
    v = Application.Match(virtColumn3(i, 1), virtColumn6, 0)
    If Not IsError(v) Then virtColumn7(i, 1) = v
Next i

Upvotes: 2

stenci
stenci

Reputation: 8481

It crashes because the Match function doesn't find the item.

You could use On Error Resume Next and then check the value of Err to see if the match was found.

Also, you don't need to Dim Column1() As Variant. A variant can be an array, so Dim Column1 As Variant it's cleaner.

Upvotes: 1

Related Questions