Problems with WorksheetFunction.Index and WorksheetFunction.Match

[Luke]

I've used Index and Match in Excel for some time to take a value from one
worksheet, find it on another and return a different value, looking something
like "Index(range,Match(value,range,0),1)". I am trying to do the same thing
in VBA, but haven't quite got the hang of it. I keep getting an
"Application-defined or object-defined error". Here's what I have (using
some abbreviations for space):

If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cells(LastRow, 3)),
App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(CurrRow,2),
Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then
GoTo Line

Feel free to point me toward a more efficient way of doing this. Otherwise,
I would greatly appreciate it if someone could point out why VBA doesn't like
me or my code.

 

[Per Jessen]

Hi

First, you should always copy/paste your original code, as typos can be a
part of the error.

In this case you have to add an sheet reference for each range/cell object
(not tested):

Dim shA As Worksheet
Set shA = Worksheets("Sheet1")
If Application.WorksheetFunction.Index(shA.Range(shA.Cells(3, 3),
shA.Cells(LastRow, 3)),
Application.WorksheetFunction.Match(Worksheets("Sheet2").Range("A2").Offset(CurrRow,
2), shA.Range(shA.Cells(3, 1), shA.Cells(LastRow, 1)), 0), 1) = Value Then
GoTo Line

Regards,
Per

 

[Luke]

Thank you for your help. I added the Dim statement and used Set as you
described. Instead of the original error message, I am now getting the
following:

Method 'Range' of object '_Worksheet' failed

 

[Dave Peterson]

Along with Per's suggestion of qualifying your ranges, I'd break it into smaller
pieces.

Dim res as variant 'could be an error
dim myRng as range
dim wks as worksheet
dim myCell as range
Dim CurrRow as long
dim SomeValue as variant

SomeValue = "whatever you're checking"

CurrRow = 1 'whatever...

set mycell = worksheets("sheet2").range("A2").offset(currRow,2)

set wks = worksheets("Sheet1")

with wks
set myrng = .range("A3",.cells(lastrow,"A"))
end with

res = application.match(mycell.value, myrng, 0)

if iserror(res) then
'there is no match
'what should happen
else
if myrng.cells(1).offset(res-1).value = SomeValue then
'do nothing
else
'do something else
end if
end if

=========
I'm not sure why you'd declare some variable for Worksheets (wkshts doesn't save
me that much typing). Maybe you're qualifying the workbook???

I'd just use:

with workbooks("Someworkbookhere.xls")
set wks = .worksheets("sheet1")
'...
end with


And notice that I didn't use the worksheetfunction qualifier with the match()
line. I used application.match(). When I use this syntax, I can check for an
error being returned.

If I used
application.worksheetfunction.match()

I'd have to code around a run-time error:

on error resume next
somevar = application.worksheetfunction.match(...)
if err.number <> 0 then
err.clear
'no match
'do the no match stuff
else
'do the match stuff here
end if
on error goto 0

=====
And since application.index() or application.worksheetfunction.index() is
essentially an offset from the first cell in range.

I used:
myrng.cells(1).offset(res-1).value

But I could have used:
myrng(res).value
too.

 

[Dave Peterson]

Ps. I wouldn't use "Value" as a variable name. Excel/VBA may allow it, but it
would confuse the heck out of me!