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(ThisW orkbook.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(ThisW orkbook.Worksheets(i).Range("A"
& j), Range("MeetingIDs"), 0)))

Does anyone have any suggestions?

Thanks,
Steve

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!


wrote:

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(ThisW orkbook.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(ThisW orkbook.Worksheets(i).Range("A"
& j), Range("MeetingIDs"), 0)))

Does anyone have any suggestions?

Thanks,
Steve

--

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
on error goto 0 '<-- added!



Dave Peterson wrote:

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!

wrote:

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(ThisW orkbook.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(ThisW orkbook.Worksheets(i).Range("A"
& j), Range("MeetingIDs"), 0)))

Does anyone have any suggestions?

Thanks,
Steve

--

Dave Peterson

--

Dave Peterson

On May 19, 9:25*pm, Dave Peterson wrote:
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
on error goto 0 '<-- added!





Dave Peterson wrote:

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!

wrote:

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(ThisW orkbook.Worksheets(i).Ran*ge("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(ThisW orkbook.Worksheets(i).Ran*ge("A"
& j), Range("MeetingIDs"), 0)))

Does anyone have any suggestions?

Thanks,
Steve

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dropping the .WorksheetFunction worked! Thanks so much!