How to MATCH in the first column of a 2 dimensional array

user_7793916 0
Viewed 357

Is there a way to reference the first column of the array kw30array in this match lookup? I read that if you set the row to 0 then it will only reference the column but after trying this it did not work. ie. kw30array(0,1) as the range.

Also, is it necessary to state 'worksheetfunction' since I am using an array and not the worksheet?

Thank you

For i = 2 To kw90rowcount
j = Application.WorksheetFunction.Match(kw90array(i, 1), kw30array, 0)
If IsError(j) Then
    kw90array(i, 32) = "-"
    kw90array(i, 33) = "-"
    kw90array(i, 34) = "-"
    kw90array(i, 37) = "-"
    kw90array(i, 38) = "-"
Else:
    kw90array(i, 32) = kw30array(j, 10)
    kw90array(i, 33) = kw30array(j, 12)
    kw90array(i, 34) = kw30array(j, 14)
    kw90array(i, 37) = kw30array(j, 11)
    kw90array(i, 38) = kw30array(j, 17)
End If
Next i
vba excel
1 Answers

Application.Match Faster with a Range

  • You have to use Application.Match to be able to use IsError or IsNumeric.
  • If Application.WorksheetFunction.Match 'doesn't find a match', a run-time error will occur. Handling this is more complicated so I abandoned it for good.
  • Here's one way to handle your issue:
Dim kw30first As Variant: kw30first = Application.Index(kw30array, 0, 1)
Dim rIndex As Variant
Dim i As Long

For i = 2 To kw90rowcount
    rIndex = Application.Match(kw90array(i, 1), kw30first, 0)
    If IsNumeric(rIndex) Then
        kw90array(i, 32) = kw30array(rIndex, 10)
        kw90array(i, 33) = kw30array(rIndex, 12)
        kw90array(i, 34) = kw30array(rIndex, 14)
        kw90array(i, 37) = kw30array(rIndex, 11)
        kw90array(i, 38) = kw30array(rIndex, 17)
    Else
        kw90array(i, 32) = "-"
        kw90array(i, 33) = "-"
        kw90array(i, 34) = "-"
        kw90array(i, 37) = "-"
        kw90array(i, 38) = "-"
    End If
Next i
  • A few days ago I learned from Tim Williams that Application.Match is about 10 times slower on an array than on a range. Shortly after I proved it. So you should consider doing something like this:
   Dim fcrg As Range: Set fcrg = kw30rng.Columns(1) ' First Column Range
   '''
   rIndex = Application.Match(kw90array(i, 1), fcrg, 0)

But still, use the array kw30array in the If statement.

user_98140690
Related