Simple loop match

[HWL]

Okay, I think I'm just not getting it. I'm really trying to do something real simple. I'm a master at Excel functions, not so good at VBA.

With functions, I use index and match instead of vlookup.

SAMPLE:
if (iserror(index(sheet1range,match(criteria, sheet1range, false),1)),"",index(sheet1range,match(criteria, sheet1range, false),1))

That would return blank if the criteria wasn't found or if matched, it would return the contents of any column row match on sheet1.

I want to do this in VBA but when it encounters no match it crashes.

Here is my VBA code. It crashes when there is no match. tempfile1 and tempfile2 are named earlier so that is not the issue.

For x = 2 To srowend
If WorksheetFunction.IsError(WorksheetFunction.Index(tempfile2.Sheets("INS Variances").Range("m1:m500"), WorksheetFunction.Match(tempfile1.Sheets("INS Variances").Cells(x, 1), tempfile2.Sheets("INS Variances").Range("A1:A500"), False), 1)) Then
Else
mtest = WorksheetFunction.Index(tempfile2.Sheets("INS Variances").Range("m1:m500"), WorksheetFunction.Match(tempfile1.Sheets("INS Variances").Cells(x, 1), tempfile2.Sheets("INS Variances").Range("A1:A500"), False), 1)
MsgBox mtest
End If
Next x
​

 

[VoG]

Possibly

If IsError(Application.Index(tempfile2.Sheets("INS Variances").Range("m1:m500"), Application.Match(tempfile1.Sheets("INS Variances").Cells(x, 1), tempfile2.Sheets("INS Variances").Range("A1:A500"), False), 1)) Then

 

[HWL]

Possibly

If IsError(Application.Index(tempfile2.Sheets("INS Variances").Range("m1:m500"), Application.Match(tempfile1.Sheets("INS Variances").Cells(x, 1), tempfile2.Sheets("INS Variances").Range("A1:A500"), False), 1)) Then

Seems to work, weird since it is just a chage in appl error and vba iserror
Thanks much

 

[VoG]

It is the change to Application that makes it work. See http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

 

[HWL]

ah okay, this is going to be a real useful piece of code for the work I do. Thanks much