Excel How to code a Vlookup? :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

How to code a Vlookup?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

archerks
Board Master


Joined: 06 Jun 2002
Posts: 104


Status: Offline

 Reply with quote  

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

Post Mon Jun 17, 2002 8:06 pm 
 View user's profile Send private message Send e-mail

Nimrod
MrExcel MVP


Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

 Reply with quote  

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


_________________
NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
Adieu,Nimrod
[ This Message was edited by: Nimrod on 2002-06-17 17:26 ]

Post Mon Jun 17, 2002 9:25 pm 
 View user's profile Send private message AIM Address

Nimrod
MrExcel MVP


Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

 Reply with quote  

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

_________________
NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
Adieu,Nimrod
[ This Message was edited by: Nimrod on 2002-06-17 19:09 ]

Post Mon Jun 17, 2002 11:09 pm 
 View user's profile Send private message AIM Address

archerks
Board Master


Joined: 06 Jun 2002
Posts: 104


Status: Offline

 Reply with quote  

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

Post Tue Jun 18, 2002 12:03 am 
 View user's profile Send private message Send e-mail

Nimrod
MrExcel MVP


Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

 Reply with quote  

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.

_________________
NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
Adieu,Nimrod

Post Tue Jun 18, 2002 12:21 am 
 View user's profile Send private message AIM Address

archerks
Board Master


Joined: 06 Jun 2002
Posts: 104


Status: Offline

 Reply with quote  

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

Post Tue Jun 18, 2002 12:59 am 
 View user's profile Send private message Send e-mail

Nimrod
MrExcel MVP


Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

 Reply with quote  

Things to check


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


_________________
NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
Adieu,Nimrod
[ This Message was edited by: Nimrod on 2002-06-17 21:12 ]

Post Tue Jun 18, 2002 1:11 am 
 View user's profile Send private message AIM Address
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.