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?
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