VBA- Worksheet event multiple vlookup

[cidfidou]

Hi excel Masters,

Can any of you provide a VBA code in order to populate some vlookups value only once any cells has been changed in a specific range. (tab calculation column A1 to A100)

Data source in tab Data (Name, Age, Sex, Country, town....) from col A to E

In Tab calculation for example if I change any name in Col A1 to A100, I would like relevant col B to E to be populated with info from data source Tab.

Also, if the user delete one name all the relevant attributes need to be blank...

Thanks in advance

 

[Fishboy]

Hi excel Masters,

Can any of you provide a VBA code in order to populate some vlookups value only once any cells has been changed in a specific range. (tab calculation column A1 to A100)

Data source in tab Data (Name, Age, Sex, Country, town....) from col A to E

In Tab calculation for example if I change any name in Col A1 to A100, I would like relevant col B to E to be populated with info from data source Tab.

Also, if the user delete one name all the relevant attributes need to be blank...

Thanks in advance

Hi cidfidou,

First make a COPY of your workbook to test in. Next you can try this worksheet_change macro which can be added directly to the back end of your "Calculation" sheet (right-click the tab name, select View Code, copy / paste in my code). I have had to improvise on some of the ranges, but if this works let let me know the specifics and we can possibly tweak the code to be more efficient.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' If a cell is updated in range A1:A1000 then...
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        ' If the new target value is not blank then...
        If Target.Value <> "" Then
            ' Do a quick count and if the new target value exists in your list of people then...
            If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("A1:A1000"), Target.Value) <> 0 Then
                ' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
                Range("B" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 2, False)
                Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 3, False)
                Range("D" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 4, False)
                Range("E" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 5, False)
            ' Else if the name did not exist in your list of people then...
            Else
                ' Display an error instructing user to check they have entered the correct name
                MsgBox "Name does not exist - please check and try again"
                ' Disable events to prevent infinite loop issues
                Application.EnableEvents = False
                ' Clear the target value
                Target.ClearContents
                ' Reselect the target cell
                Target.Select
                ' Re-enable events
                Application.EnableEvents = True
            End If
        ' Else if the target value is blank then...
        Else
            ' Clear columns B to E of the target row
            Range("B" & Target.Row, "E" & Target.Row).ClearContents
        End If
    End If
End Sub

 

[cidfidou]

This is working great!!!! Thank you so much. You made my day.

I am always really pleased and amazed with the help i have received from people like you...THANKS

 

[cidfidou]

of the Worksheet function class..

Let me know the assumption you made if that helps.... Thanks again asI really appreciate your help

 

[Fishboy]

of the Worksheet function class..

Let me know the assumption you made if that helps.... Thanks again asI really appreciate your help

Hmm, without seeing your workbook I can only speculate but I would imagine it will be one of the following possible causes:

1. The name of the sheet you are looking up from in not exactly "Data Source"
2. On the sheet you are looking up from the lookup values are not in the same range as I have specified (columns A:E, starting in A2)
3. Some other unknown anomaly which I can't be sure of without seeing the actual workbook

If you can confirm whether or not points 1 and 2 could be the cause and if not, could you possibly upload a copy of your workbook to a file hosting site and share a link to it so we can see the document layout etc?

 

[cidfidou]

Hi Fishboy,

I tried to delete my post but could only edit it and I think now there was a timing issue.. Your code works great. Thank you so much!!!!!

 

[Fishboy]

Hi Fishboy,

I tried to delete my post but could only edit it and I think now there was a timing issue.. Your code works great. Thank you so much!!!!!

Yay!

Happy to help

 

[cidfidou]

Hi fishboy,

I have been trying in vain to tweak your code to remove the error message when a Name is not found...

How can I remove the count and just display n/a when a name is not found?

Thanks in advance

 

[Fishboy]

Hi fishboy,

I have been trying in vain to tweak your code to remove the error message when a Name is not found...

How can I remove the count and just display n/a when a name is not found?

Thanks in advance

To be honest I we are not putting the actual VLOOKUP formula in the cells I am not 100% certain how to handle values not being found (I'm still learning all this myself).

That said however we can tweak my code so that if a name is entered that isn't in the list (and therefore the lookup wont find anything) we can make the adjacent cells fill with "N/A"

Private Sub Worksheet_Change(ByVal Target As Range)
    ' If a cell is updated in range A1:A1000 then...
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        ' If the new target value is not blank then...
        If Target.Value <> "" Then
            ' Do a quick count and if the new target value exists in your list of people then...
            If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("A1:A1000"), Target.Value) <> 0 Then
                ' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
                Range("B" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 2, False)
                Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 3, False)
                Range("D" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 4, False)
                Range("E" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E1000"), 5, False)
            ' Else if the name did not exist in your list of people then...
            Else
                ' Update the detail cells with "N/A"
                Range("B:E" & Target.Row).Value = "N/A"
            End If
        ' Else if the target value is blank then...
        Else
            ' Clear columns B to E of the target row
            Range("B" & Target.Row, "E" & Target.Row).ClearContents
        End If
    End If
End Sub

 

[cidfidou]

Hey fishboy,

Cheers mate Again.

I just replaced the line for the extra NA line you added as for some reason Range("B:E" & Target.Row).Value = "N/A" did not work. (i used the same a the clear content range and it worked)

Could you please show me how to use the below line of code for non continuous cells (for example col A,B and D)?
Range("B:E" & Target.Row).Value = "N/A"