Multiple IF Statement

GonzoB

New Member
Joined
Dec 4, 2021
Messages
37
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?

VBA Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How about
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,196,101
Messages
6,013,459
Members
441,767
Latest member
Craigh4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top