Application.Match and Ranges

  • Thread starter Thread starter gimme_this_gimme_that
  • Start date Start date
G

gimme_this_gimme_that

I'd like to get the following clip of code to work:

I fathom that Excel is bulking because it can't use a compare a range
against a range?

Any suggestions?

Thanks.


Set ws = book.Sheets("WorkAResultSet")
Set wr = ws.Range("A1", "B" + Format(Utils.RowCount(ws)))
For itr = 1 To 3
t.Cells(1, itr + 8).Value = UCase(ws.Cells(1, itr).Value)
Next itr
For itr = 2 To nw
res = Application.Match(t.Range(t.Cells(itr, 1), t.Cells(itr, 2)),
wr, 0)
For j = 1 To 5
t.Cells(itr, j + 8).Value = ws.Cells(res, j).Value
Next j
Next itr
 
This worked for me

Sub test()
Dim rngArray As Range, rngValue As Range
Dim vRes As Variant

Set rngArray = Range("a1:a10")
Set rngValue = Range("c5")

vRes = Application.Match(rngValue, rngArray, 0)
If VarType(vRes) = vbError Then
MsgBox "no match"
Else
MsgBox vRes
End If

End Sub

Not possible to second guess what your variables refer to and why your code
fails

Regards,
Peter T
 
Thanks Peter,

I think you example works because mgValue is a single cell.
 
Back
Top