A simple loop I've setup to run down a range on an active worksheet to count values is erroring when it hits any #N/A value. I'm using the the "For c Range(A3: A500)..next" looping approach and setting on variable to the column in the range to c.value and an adjacent column to c.offset(0,1).value. I run an If asking if variable = "Yes", or = "No".
Works if the values are blank or a valid string just not if #N/A from a vlookup!
I've tried setting the variable as string or variant. Get two different error #s.
I've also tried using If IsNull(variable) or IsEmpty(variable) or If variable = "" without success.
Any suggestings for handling these pesky #N/A values???!!!!
(Sorry I can paste in the code. It's on a PC that's restricted and I can't take anything off it and rather not retype it here. Sorry.)
Please help...somebody. Thanks!
Are you getting a Type Mismatch Error?
This will occur when you compare an Error type to a String Type value ?Error 2042 = "Yes".
You can avoid this by definining your variable as a String at the top of your Sub or Function:
Dim strVar as String
Or, You can check the Cell Value with the isError() function
Or, You can use cStr() to convert the variable value to a Text string.