Catch error

[Karen53]

Hi,

I'm trying to catch if the value is not there but am unsuccessful. What
have I missed. The IsError does not catch the error and so it errors on the
TMRnowLMR line when it does not match.

Set rngMatch = Sheets(ShName).Range(StartCol & StartRow & ":" & _

StartCol & EndRow)

For iCtr = StartRow To EndRow
Debug.Print "Blocked iCtr " & iCtr

If (IsError(Application.Match(Sheets(ShName).Range(StartCol &
iCtr), _

rngMatch, 0))) Then
Sheets(ShName).Range(LMRCol & iCtr).Value = "NA"
GoTo Continue1
End If

TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol & _
iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"),
4, False)
Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR
Continue1:
Next

 

[Karen53]

Never mind. I've got it.

If TrafficType = "Blocked2" Then

For iCtr = StartRow To EndRow
Debug.Print "Blocked iCtr " & iCtr
TMRnowLMR = 0

On Error Resume Next
TMRnowLMR = Application.VLookup(Sheets(ShName).Range(StartCol &
iCtr).Value, Sheets(ShName).Range("WebBlockedTMR"), 4, False)
If TMRnowLMR > 0 Then
Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR
Else
Sheets(ShName).Range(LMRCol & iCtr).Value = "NA"
End If
Next
End If

 

[Bob Phillips]

It is hard to help with the little info you have supplied.

Are StartCol and StartRow both numeric variables, because that won't work.
What problems are you getting?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Karen53]

Hi Bob,

No, one variable is a string for the column and one is Long for the row
number.

The problem is it doesn't catch the error and errors out on the TMRnowLMR =
line instead, because there is no match on that ictr. Those that match
before the error process ok because they match.
--
Thanks for your help.
Karen53

It is hard to help with the little info you have supplied.

Are StartCol and StartRow both numeric variables, because that won't work.
What problems are you getting?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

If you declare TMRnowLMR as a variant, you can drop the "On error" stuff:

Dim TMRnowLMR as variant 'could be an error
...
If TrafficType = "Blocked2" Then
For iCtr = StartRow To EndRow
Debug.Print "Blocked iCtr " & iCtr
TMRnowLMR = Application.VLookup( _
Sheets(ShName).Range(StartCol & iCtr).Value, _
Sheets(ShName).Range("WebBlockedTMR"), 4, False)
if iserror(tmrnowlmr) then
'no match
Sheets(ShName).Range(LMRCol & iCtr).Value = "NA"
else
Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR
End If
Next
End If

=====
You could check for a number, too:

If isnumeric(TMRnowLMR) Then
Sheets(ShName).Range(LMRCol & iCtr).Value = TMRnowLMR
Else
Sheets(ShName).Range(LMRCol & iCtr).Value = "NA"
End If

Hi Bob,

No, one variable is a string for the column and one is Long for the row
number.

The problem is it doesn't catch the error and errors out on the TMRnowLMR =
line instead, because there is no match on that ictr. Those that match
before the error process ok because they match.