Multiple IF Statement

[GonzoB]

Hi Helpers,

I have an existing, working VBA which I need to extend now to cover a use case which my VBA has not had to cover before. The below code populates the 7th column on Sheet2 with values which are vlook'd up from Sheet1. If there's no hit "Empty" will be put into the target field. If there's a hit, whatever is in the source field that will be put into the target field. This latter I need to enhance now. Namely: if the vlookup has a hit and the source field is blank then value "Empty" needs to be put into the target field. Currently in such a case the target field is blank.

Do you have any suggestions?

Dim lookupVal_5 As Range, myString_5 As Variant, Rng_5$
On Error Resume Next
    Set lookupVal_5 = Sheets("Sheet2").Cells(i, 15)
    Rng_5 = Range(Cells(2, 1), Cells(Sheet1Row, 7)).Address
    myString_5 = WorksheetFunction.VLookup(lookupVal_5, Sheets("Sheet1").Range(Rng_5), 7, False)
    If Err.Number > 0 Then
        Sheets("Sheet2").Cells(i, 7) = "Empty"
        Err.Clear
    Else
        Sheets("Sheet2").Cells(i, 7) = myString_5
    End If

 

[Fluff]

How about

Dim lookupVal_5 As Range, myString_5 As Variant, Rng_5$
    Set lookupVal_5 = Sheets("Sheet2").Cells(i, 15)
    Rng_5 = Range(Cells(2, 1), Cells(Sheet1Row, 7)).Address
    myString_5 = Application.VLookup(lookupVal_5, Sheets("Sheet1").Range(Rng_5), 7, False)
    If IsError(myString_5) Then
        Sheets("Sheet2").Cells(i, 7) = "Empty"
    ElseIf myString_5 = "" Then
        Sheets("Sheet2").Cells(i, 7) = "Empty"
    Else
        Sheets("Sheet2").Cells(i, 7) = myString_5
    End If

 

[GonzoB]

Hi Fluff,

thanks for your response. Do you think performance wise using IsEmpty would be better? I was trying to use this function but could not make it work so far. Both my source and target Sheets are huge, hence I'm worried about speed.

 

[Fluff]

Do you think performance wise using IsEmpty would be better?

In place of what?

 

[GonzoB]

myString_5 = ""

 

[Fluff]

You cannot use IsEmpty for that.
If you are worried about speed, then you should not be using worksheet functions inside a loop as they are slow.