IF, ELSEIF Loop Not working. PLEASE HELP!

[navidadi28]

Hello,

I am attempting a loop vlookup with an IF statement. The Vlookup is matching loans # (in i,4) from the active sheet to the lookup sheet "CADM" to return the "Commitment Amounts" to the active sheet cell (i,5) and down. I have the Vlookup working just fine. The problem is when I want to do the IF statement. The reason for the IF statement is that not all the loan # will match and return a Commitment Amount. So IF the return value is 0 (this is in cell i,5) then have the cell (i,5) be the value of the cell (i,6). Then I have an ElseIf being if cell(i,5) return #N/A then have cell(i,5) be the value of cell(i,6).

Please note for the IF/ElseIF statement the values "0" and "#N/A" will be in random order. N/A might come first for this month but next month the 0 will come first.

Sub CADMVlookup()


Dim LastRow As Long
Dim i As Integer

LastRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 8 To LastRow
'This is the vlookup that is working correctly
Cells(i, 5).Value = Application.vlookup(Cells(i, 4), Worksheets("CADM").Range("B:C"), 2, False)

This is where my problem starts.
'This is the start of the my If statement.
If (Cells(i, 5).Value = 0) Then
Cells(i, 5).Value = Cells(i, 6).Value
If Cells(i, 5).Value = "#N/A" Then
Cells(i, 5).Value = Cells(i, 6).Value
End If
Next i


End Sub


Thank you so much for the help!

 

[navidadi28]

For reference the error that is given is "Type mismatch" with the following line highlighted.

If (Cells(i, 5).Value = 0) Then

 

[nils7]

One thought is to change the second if statement into an "ElseIf" statement.

If (Cells(i, 5).Value = 0) Then
Cells(i, 5).Value = Cells(i, 6).Value
ElseIf Cells(i, 5).Value = "#N/A" Then
Cells(i, 5).Value = Cells(i, 6).Value
End If

 

[navidadi28]

One thought is to change the second if statement into an "ElseIf" statement.

If (Cells(i, 5).Value = 0) Then
Cells(i, 5).Value = Cells(i, 6).Value
ElseIf Cells(i, 5).Value = "#N/A" Then
Cells(i, 5).Value = Cells(i, 6).Value
End If

Still shows the same error. Any other suggestion/ Thank you!

 

[Osvaldo Palmeiro]

Try

If Cells(i, 5).Value = 0 Then

 

[navidadi28]

Try

If Cells(i, 5).Value = 0 Then

It is the same result with the () or without. It does complete the task of replacing 0 value cells with the value from cells(i,6) however, the macro still stops and Error 13 appears "Type Mismatch"

 

[Osvaldo Palmeiro]

If IsError(Cells(i, 5).Value) Then
 Cells(i, 5).Value = Cells(i, 6).Value
ElseIf Cells(i, 5) = 0 Then
 Cells(i, 5).Value = Cells(i, 6).Value
End If