Selection change and vlookup

[orador]

Hello All,

I am really new to VBA and need your help.

I am trying to use SelectionChange to click on a cell and then use vlookup to show contents of an adjacent cell - to do this I made a reference to the selection address in the worksheet that then runs the vlookup from the worksheet--the code below works, but I cannot stops the screen flicker even using the screenupdating=false, so I think I need to put eh vlookup in the VBA itself?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
[AB2] = Selection.Address
Application.ScreenUpdating = True
End Sub
""'in cell E6 on the wsheet I have =VLOOKUP($AB$2,lookup,3,FALSE)
any help on this is much appreciated

 

[Domski]

Hi,

So as you click on a cell you want to look up the value in that cell in the table Lookup and transfer the value from the 3rd column to E6?

Dom

 

[orador]

Hi Dom

Thanks fo rhte response...

sort of -I am using the cell reference from the range I can select as the first column on the vlookup, so it checks the cell ref against that and shows me 3rd column. Essentally what I am trying to do is click on a cell in a column range and I have a window at the top of the sheet that dsiplays details of an item.
Hope this make it more clear

ken

 

[Domski]

Not sure I fully understand still but maybe something like:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Range("A1") = WorksheetFunction.VLookup(target.Value, Sheets("Sheet2").Range("Lookup"), 3, False)
End Sub

Change sheet name to the one with your lookup table on and Range("A1") to the cell you want to populate with the result.

Dom

 

[orador]

Not sure I fully understand still but maybe something like:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Range("A1") = WorksheetFunction.VLookup(target.Value, Sheets("Sheet2").Range("Lookup"), 3, False)
End Sub

Change sheet name to the one with your lookup table on and Range("A1") to the cell you want to populate with the result.

Dom

thanks Dom I'll give it a go!

Ken

 

[orador]

Not sure I fully understand still but maybe something like:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Range("A1") = WorksheetFunction.VLookup(target.Value, Sheets("Sheet2").Range("Lookup"), 3, False)
End Sub

Change sheet name to the one with your lookup table on and Range("A1") to the cell you want to populate with the result.

Dom

Hi Dom

Changing target value to seleciton address it Works Thanks, but still get the screen flicker when I click on the cells. Tried screen updating =false again, but this doesn't seem to do anything - code is below - if you can hlep with this..

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Intersect(target, Range("qtycolumn")) Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
[E6] = Selection.Address
Range("E6") = WorksheetFunction.Vlookup(Selection.Address, Sheets("ITEM DETAIL").Range("lookup"), 3, False)
Application.ScreenUpdating = True
End If
End Sub