test for #VALUE in vba

[jdm21788]

Hi folks,

I am getting a type mismatch on the following line of code:

If IsError(Cells(r, 4).Value + 1) And Cells(r, 5).Value <> "" Then

I have also tried:

If IsError(Cells(r, 4).Value + 1) = True And Cells(r, 5).Value <> "" Then

I want the If condition to be true if Cells(r, 4).Value + 1 = #VALUE and if Cells(r, 5).Value is not blank.

Thanks in advance!

Jonathan

 

[dfenton21]

If Cells(r, 4).Value = #VALUE, you can't add 1 to an error.

I suggest doing your error check before adding 1

Damien

 

[Scott Huish]

If Cells(r, 4).Text = "#VALUE!" And Cells(r, 5) = "" Then

 

[jdm21788]

Thanks for the responses, HOTPEPPER and dfenton21. The point of adding 1 is sort of an error check in itself. I am outputting into a template from SAS and it is automatically outputting either a 2 digit number or 2 blanks. By adding 1 to the blanks, it triggers the error. (I can't make the condition > 0 because the number is sometimes 0.) I found the solution, though, I was making it much more difficult than it needed to be. I simply look for the value to = " " or, in other words "_ _" / 2 blanks. Thanks again!

 

[Scott Huish]

Why can't you use IsNumeric?

 

[hellebore08]

dfenton21 is correct to do your error check before adding 1.

Please try and check if this does the same trick as you wanted.

If Application.WorksheetFunction.IsText(Cells(r, 4)) And Cells(r, 5) = "" Then

You can see that i have removed the ".value + 1" part of the statement because it is the cause of the error (as what dfenton21 said). Also, I have used the IsText (from HOTPEPPER's idea*) function since cells(r,4).value + 1 will trigger error only when it is not a number (I hope this is same with the criteria you need).

cheers!

-e.rgabrieldoronila

*but you can't use IsNumeric without using it as UDF. You can use IsNumber function instead.