Error checking in Excel VBA

[phudgens]

I am attempting to incorporate some error checking in my code to check for cells containing "#NA", which otherwise cause problems for my code below. My first attempt: rng5.Errors.Item(xlEvaluateToError), runs but does not work. Does anyone know why?


For Each rng5 In History1Column
If rng5.Errors.Item(xlEvaluateToError) = True Then GoTo NextRng5
If Mid(LCase(Trim(rng5.Value)), 1, 7) = "service" Then SP = rng5.Offset(0, 1).Value
If Mid(LCase(Trim(rng5.Value)), 1, 15) = "max 30 day ip =" Then MaxIP = rng5.Offset(0, 1).Value
NextRng5:
Next rng5


Thanks,
Paul H.
Denver

 

[SkipVought]

Hi,

And much better to avoid GOTO branches and use structured code, IMHO...

                For Each rng5 In History1Column
                    If [b]Not Application.ISNA(rng5.value)[/b] = True Then 
                       If Mid(LCase(Trim(rng5.Value)), 1, 7) = "service" Then SP = rng5.Offset(0, 1).Value
                       If Mid(LCase(Trim(rng5.Value)), 1, 15) = "max 30 day ip =" Then MaxIP = rng5.Offset(0, 1).Value
    [b]                 End If[/b]
                Next rng5

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

Skip,

Thanks alot - it worked! I searched various places to find a complete list of the "IS" functions, but can find only abbreviated lists, and none that contain the ISNA function. Do you know of a website that has a complete list?

Thanks,
Paul H.

 

[phudgens]

Never mind - I just found the list in online help.

THanks,
Paul H.

 

[SkipVought]

It is an Excel Shreadsheet function, not a VBA function. So you would not find it in VBA Help. Rather in Excel Help.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}