CODEWITHSUNDEEP
vbaexcel
Jordan
Jordan

Reputation: 363

Why is my If statement returning #VALUE?

I have a portion of code that trims a word from a cell that may contain multiple words. It then places this single word into another cell. Sometimes the cell which contains multiple words contains only one word, so when it trims, I get #VALUE! returned in the cell.

I will get the same returned value if it tried to trim a blank space which in this case is "_______________". It evaluates the cell looking for #VALUE!, and if it exists, checks the cell containing multiple words to see if the reason is because it is blank "________________" or is a single word.

I have used this code below to accomplish this but it is now ignoring the AND portion of the code and immediately performs the action if the cell reads #VALUE! regardless of the second condition.

Does anyone know what may cause this?

'Trims the full name
    Cells(i + 2, 10).FormulaR1C1 = _
        "=TRIM(RIGHT(SUBSTITUTE(REPLACE(RC[10],FIND(""/"",RC[10])-0,LEN(RC[10]),""""),"" "",REPT("" "",255)),255))"

    'Replaces Error with a blank ***Sometimes there are no defect 2 defects which causes this error
If Cells(i + 2, 10) = "#VALUE!" And Cells(i + 2, 20) = "________________________________________________________________________________" Then
Cells(i + 2, 10) = ""
Else
End If

'Replaces Error with a blank ***Sometimes there are no defect 3 which causes this error
If Cells(i + 2, 10) = "#VALUE!" Then
Cells(i + 2, 10) = Cells(i + 2, 20)
Else
End If

Upvotes: 0

Views: 437

Answers (2)

EvR
EvR

Reputation: 3498

Instead of putting a formula in a cell by VBA, I would do this formula-work in your procedure itself.

With Cells(i + 2, 10)
    If InStr(.Offset(, 10).Value, "/") > 0 Then
       wrds = Split(Split(.Offset(, 10), "/")(0), " ")
       .Value = wrds(UBound(wrds))
    Else
      .Value = .Offset(, 10).Value 'or ""
    End If
End With

I assume these underscores are already eliminated

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Formula

Not sure why you need to treat a series of underscores as a blank value when vbNullString or "" would normally do a much cleaner job; assuming we're stuck with these underscores, I'd start by eliminating them with SUBSTITUTE(<range>,"_",""), and work off that - if the cell only contained underscores, the result of that function would be an empty string.

So a predicate like LEN(SUBSTITUTE(<range>,"_",""))=0 would tell us if we're looking at a <range> that needs to be considered as empty.

Therefore IF(LEN(SUBSTITUTE(<range>,"_",""))=0, <value if empty>, <value if not empty>) would be a "skeleton" formula to build on IMO, unless I'm missing something (which is rather likely).

#VALUE!

A #VALUE! cell error isn't a string that says "#VALUE!" - it's an error, of type Error, and these can't be compared to a String literal (or anything for that matter).

So this statement is guaranteed to throw a type mismatch error if the cell actually contains an error:

If Cells(i + 2, 10) = "#VALUE!" Then

Which means you must have On Error Resume Next at the top of that snippet, which means your code is effectively ignoring run-time errors and happily running along in an error state - that's quite likely why the statements execute unconditionally.

Proof:

Sub test()
    On Error Resume Next
    If 12 / 0 > 1 And False Then ' throws error 11 "division by zero"
        Debug.Print "And False was ignored!" 'runs unconditionally
    End If
End Sub

Remove On Error Resume Next, it's not helping.

What you want is to verify whether the cell contains an error - use the IsError function for that:

If IsError(Cells(i + 2, 10)) Then

If you need to check specifically for #VALUE! error, you can do it like this:

If Cells(i + 2, 10).Value = CVErr(xlErrValue) Then

However that's kind of moot, because the #VALUE! error comes from the formula you've put in - use the IFERROR Excel function in your formula to provide a value in case of error:

=IFERROR(<formula>, <value to return formula results in an error>)

You could have your fallback value right there.

That said String(n, "_") would give you a string with n underscores. That would probably be much easier to debug than dealing with a string literal that literally has the n underscores in it. Much easier to do String(80,"_") than actually count the underscores and risk accidentally having 79 or 81.

Upvotes: 1

Related Questions