CODEWITHSUNDEEP
excelvba
PeteMcKinnon
PeteMcKinnon

Reputation: 15

ElseIf formula to check if cell is empty and if so to blank out other cells

I have created a quoting spreadsheet, within the VBA Code I have the below:

Sub MatchI19()
If Range("J19").Value = "IWA" Then
    Range("K19").Value = "IWA"
    ElseIf Range("J19").Value = "IWK" Then
    Range("K19").Value = "IWK"
    ElseIf Range("J19").Value = "IWVD" Then
    Range("K19").Value = "IWVD"
End If
End Sub
Sub IndexMatchI19()
If Range("K19").Value = "IWA" Then
Range("L19") = "=INDEX(IWA!C:C,MATCH(I19,IWA!E:E,0))"
Range("M19") = "=INDEX(IWA!B:B,MATCH(I19,IWA!E:E,0))"
Range("N19") = "=INDEX(IWA!F:F,MATCH(I19,IWA!E:E,0))"
ElseIf Range("K19").Value = "IWK" Then
Range("L19") = "=INDEX(IWK!C:C,MATCH(I19,IWK!E:E,0))"
Range("M19") = "=INDEX(IWK!B:B,MATCH(I19,IWK!E:E,0))"
Range("N19") = "=INDEX(IWK!F:F,MATCH(I19,IWK!E:E,0))"
End If
End Sub

I would like to add another section to this. If I delete the 'Product Code' out of cell I19 after this VBA has run, I then see N/A error code in L19, M19 and N19. I would like to add a section of code, that basically sees if the cells contains no text that it blanks out cells L19, M19 and N19 or has some standard text to it to instruct the user to do something.

Has anyone got any ideas? Thank you in advance

Upvotes: 0

Views: 107

Answers (1)

PeteMcKinnon
PeteMcKinnon

Reputation: 15

Thanks to @BigBen The correct way of formatting this VBA formula is: "=IFERROR(INDEX(IWA!C:C,MATCH(I19,IWA!E:E,0)),"""")"

Upvotes: 1

Related Questions