Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup cell location (Excel 2000)

    I have a workbook that over uses the vlookup function to find data and thus slows the calculation within the workbook. I would like to replace most of the vlookup formulas with direct links to the actual cell where the value resides so I have a bullet proof audit trail and maybe speed up the calc. I use the following code to find the lookup value

    Selection.Find(What:=LUValue, After:=ActiveCell, LookIn:= _
    xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
    , MatchCase:=False).Activate


    LUValue is the vlookup value that is created from the text in two nearby cells of where the lookup formula is. What I want is the cell address of what the find function locates after taking into account the offset of the lookup. for example, if the find function locates the the lookup value on Sheet1!$C$6 and the lookup offset is 4 then I want in the cell where the lookup formula is currently to be =Sheet1!$F$6, if the offset is 5 then I the cell formula would be =Sheet1!$G$6. I should have not problem assembling the rest of the code, but since I do most of my programming in Access I sometimes need help with the excel object model.

    Thanks for any Help

    Richard Hanz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup cell location (Excel 2000)

    You can use something like this:

    Dim oCell As Range
    Dim strFormula As String
    Set oCell = Selection.Find(What:=LUValue, After:=ActiveCell, LookIn:= _
    xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
    , MatchCase:=False)
    If Not oCell Is Nothing Then
    strFormula = "=" & oCell.Offset(0, 4).Address(External:=True)
    Range(...).Formula = strFormula
    End If

    You can replace the column offset 4 with a variable if necessary.

  3. #3
    New Lounger
    Join Date
    May 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup cell location (Excel 2000)

    Thanks Hans,

    This did what I want.

    Richard Hanz

Posting Permissions

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