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
Karen53 wrote:
>
> 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
>
> "Bob Phillips" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:40A900D6-39BA-4450-881E-(E-Mail Removed)...
> > > 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
> > >
> > > --
> > > Thanks for your help.
> > > Karen53
> >
> >
> >
--
Dave Peterson
|
Similar Threads
