Error handling error # 1004 Run-time error

[ExcelQuestion123]

hello,

I'm having difficulting with a program I'm trying to write. Basically,
I want to do a vlookup or a match in vba and if it returns an error, I
want it to highlight a cell and if not, I want it to actually do the
vlookup.

Here is the code I have:

Sub replace_values()
Dim i, j As Integer

i = 1
j = 3

If
(IsError(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets(i).Range("A"
& j), Range("MeetingIDs"), 0))) Then
ThisWorkbook.Worksheets(i).Range("T" & j).Interior.ColorIndex = 4
Else: ThisWorkbook.Worksheets(i).Range("T" & j).Value =
Application.WorksheetFunction.VLookup(ThisWorkbook.Worksheets(i).Range("A"
& j), Range("StatusTable"), 2, False)
End If

End Sub

I keep getting an error (Run-time error 1004) on the first part of it:
If
(IsError(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets(i).Range("A"
& j), Range("MeetingIDs"), 0)))

Does anyone have any suggestions?

Thanks,
Steve

 

[Dave Peterson]

Try dropping the .worksheetfunction from your line of code:

if iserror(application.match(thisworkbook....

Application.match() can be tested using iserror. But
application.worksheetfunction.match() needs something like:

dim res as variant
on error resume next
res = application.worksheetfunction.match(...
if err.number <> 0 then
'no match
err.clear
else
'no error
msgbox res '???
end if

=========
ps. The same thing with application.vlookup(), too!

 

[Dave Peterson]

ps. I forgot to clean up my error handling...

dim res as variant
on error resume next
res = application.worksheetfunction.match(...
if err.number <> 0 then
'no match
err.clear
else
'no error
msgbox res '???
end if

 

[ExcelQuestion123]

ps.  I forgot to clean up my error handling...

dim res as variant
on error resume next
res = application.worksheetfunction.match(...
if err.number <> 0 then
  'no match
  err.clear
else
  'no error
  msgbox res '???
end if







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dropping the .WorksheetFunction worked! Thanks so much!