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]

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"

Hmm, I'm sure there must be a clever way of specifying an array of ranges but for the life of me I can't remember how to do it. I thought it was something like this (which doesn't work by the way)

Range(Array("A" & Target.Row, "B" & Target.Row, "D" & Target.Row)).Value = "N/A"

Unless a guru can remind me what the correct syntax for my example directly above is, you would need to split the non-contiguous ranges onto separate lines of code:

Range("A:B" & Target.Row).Value = "N/A"
Range("D" & Target.Row).Value = "N/A"
Range("F" & Target.Row).Value = "N/A"

 

[cidfidou]

You are a Star... Have a good evening!!!!

 

[Fishboy]

You are a Star... Have a good evening!!!!

You too bud

 

[cidfidou]

Hi Fishboy,

i would need your precious help again

How could I tweak the below line with an offset function in order to look up the result value of the first lookup (ie if the first lookup value for example for a given name is Town X, I would like to look up at X and not the Name entered)..

Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("Data Source").Range("A2:E5000"), 3, False)

 

[cidfidou]

This is the code I came up with but it didnt work.. Excel complains about the name of the Private sub... Not sure if we can have 2 different private ones...if I change the name of the second excel is not complaining but only the first is working... Please help as my VBA knowledge is limited and I have spent hours on line for nothing

Private Sub Worksheet_Change(ByVal Target As Range)
' If a cell is updated in range A1:A1000 then...
If Not Intersect(Target, Range("A1:A5000")) 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 PN then...
If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("A1:A5000"), 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:E5000"), 2, False)
' Else if the name did not exist in your list of people then...
Else
' Update the detail cells with "N/A"
Range("B" & 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).ClearContents
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

' If a cell is updated in range A1:A1000 then...
If Not Intersect(Target, Range("A1:A5000")) 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 PN then...
If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("B1:B5000"), Target.Value) <> 0 Then
' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Sheets("Data Source").Range("B2:E5000"), 2, False)
' Else if the name did not exist in your list of people then...
Else
' Update the detail cells with "N/A"
Range("C" & 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("C" & Target.Row).ClearContents
End If
End If
End Sub

 

[Fishboy]

Hi cidfidou,

The first thing to remember is please wrap any code you share in code tags [ CODE ] your code here [ /CODE ] (but without the spaces inside the square brackets). Doing so makes the code easier to read and will retain any indentations added to it.

The next thing to remember is that you can only have one Worksheet_Change macro per sheet. If you need more than one action then you have to join them together into a larger piece of code like follows:

Private Sub Worksheet_Change(ByVal Target As Range)
' If a cell is updated in range A1:A5000 then...
If Not Intersect(Target, Range("A1:A5000")) 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 PN then...
        If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("A1:A5000"), 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:E5000"), 2, False)
        ' Else if the name did not exist in your list of people then...
        Else
            ' Update the detail cells with "N/A"
            Range("B" & 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).ClearContents
    End If
End If


' If a cell is updated in range A1:A5000 then...
If Not Intersect(Target, Range("A1:A5000")) 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 PN then...
        If Application.WorksheetFunction.CountIf(Sheets("Data Source").Range("B1:B5000"), Target.Value) <> 0 Then
            ' Do a worksheet function to VLOOKUP the corresponding Age, Sex, Country and Town details
            Range("C" & Target.Row).Value = Application.WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Sheets("Data Source").Range("B2:E5000"), 2, False)
        ' Else if the name did not exist in your list of people then...
        Else
            ' Update the detail cells with "N/A"
            Range("C" & 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("C" & Target.Row).ClearContents
    End If
End If
End Sub

Basically I took out the END SUB from the first macro, then removed the SUB NAME from the second macro. This in effect joins them together into a single unit and should allow Excel to process both bits of code.

Can you test out the above and see if your issue is resolved?

 

[cidfidou]

Hi Fishboy,

It is working like a charm... THANK YOU so much

I have taken note to wrap any code I share in code tags [ CODE ].

 

[Fishboy]

Hi Fishboy,

It is working like a charm... THANK YOU so much

I have taken note to wrap any code I share in code tags [ CODE ].

Happy to help mate