How to code a Vlookup?

[archerks]

I have a cell G8 that has this formula in it.

=IF($L$8>"",VLOOKUP($Q$6,Customer!$B:$J,5,FALSE),"")

I would like to remove the formula from the cell and have it updated by code.

How would you state this formula in VB. I would like to have it updated anytime there is a change in Cell F3 and/or when the sheet is activated (Sheet5)

Thanks
Dave

 

[Nimrod]

This code seems to do the trick

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$F$3" And Not IsEmpty(Range("$L$8").Value) Then
SrchVal = Range("$Q$6").Value
With Worksheets("Customer").Range("B:B")
Set c = .Find(SrchVal, LookIn:=xlValues)
If Not c Is Nothing Then
Range("$G$8").Value = c.Offset(0, 4).Value
End If
End With
End If
End With
End Sub

Private Sub Worksheet_Activate()
With Target
If .Address = "$F$3" And Not IsEmpty(Range("$L$8").Value) Then
SrchVal = Range("$Q$6").Value
With Worksheets("Customer").Range("B:B")
Set c = .Find(SrchVal, LookIn:=xlValues)
If Not c Is Nothing Then
Range("$G$8").Value = c.Offset(0, 4).Value
End If
End With
End If
End With
End Sub



to install

• select sheet 5
• Right click on sheet5's Tab
• select "view code" from drop down menu
• VBE will open... paste in large Right VBE window
• go to sheet 5 and test

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2002-06-17 17:26

 

[Nimrod]

This is the same as above but put together in a more ... acceptable method



Private Sub Worksheet_Change(ByVal Target As Range)
Call BogusLookUp
End Sub

Private Sub Worksheet_Activate()
Call BogusLookUp
End Sub



Public Sub BogusLookUp()
With Target
If .Address = "$F$3" And Not IsEmpty(Range("$L$8").Value) Then
SrchVal = Range("$Q$6").Value
With Worksheets("Customer").Range("B:B")
Set c = .Find(SrchVal, LookIn:=xlValues)
If Not c Is Nothing Then
Range("$G$8").Value = c.Offset(0, 4).Value
End If
End With
End If
End With
End Sub

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2002-06-17 19:09

 

[archerks]

Thanks for your time NimRod,
When I run the code I get a

RunTime Error '424'
Object Required

When you debug it is on this line

If .Address = "$F$3" And Not IsEmpty(Range("$L$8").Value) Then

Dave

 

[Nimrod]

ouch ..... i've seen where I got distracted and messed up... clear it all and use the first version and tell me how that works.

 

[archerks]

Hi,
Tried original code. The Sub Worksheet_Activate() generates the same error. Sub Worksheet change does not generate an error but the formula doesn't seem to work either

Thank
Dave

 

[Nimrod]

Things to check

• do you have a sheet with the exact name "Customer"?
• Are you sure you put the code into the "Sheet5" object ?

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2002-06-17 21:12