How to code a Vlookup?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: How to code a Vlookup?

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  6. #6
    Board Regular
    Join Date
    Jun 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •