Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup return value (2000+)

    Is it possible using vba (application.worksheetfunction.vlookup) for the return value of a vlookup table to be a formula instead of a value --- to make myself clearer sheet1 cell "a1" calls a vlookup table found in sheet2 I want the return value to be a specific cell address ie "+z1" meaning that a later change in value of cell "z1" shall cause cell "a1" value to be changed accordingly.
    Hope I have made myself clear
    Smbs

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

    Re: vlookup return value (2000+)

    You can use cell addresses as strings in the INDIRECT function. So cell B1 could contain a formula such as

    =INDIRECT(VLOOKUP(A1,Sheet2!A1:B10,2,FALSE))

    If the value of A1 causes VLOOKUP to return "Z1", the formula will return the value of cell Z1.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup return value (2000+)

    Thanx HansV but I am not quite sure how to use the indirect function in vba
    Regards
    Smbs

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

    Re: vlookup return value (2000+)

    My reply was intended to indicate that perhaps you don't need VBA to accomplish what you want.
    If you do want to use VBA, INDIRECT is not available. Instead, you can set the formula of a cell like this:

    Dim strCell As String
    strCell = Application.WorksheetFunction.VLookup(Worksheets(" Sheet1").Range("A1"), Worksheets("Sheet2").Range("A1:B10"), 2, False)
    Worksheets("Sheet1").Range("B1").Formula = "=" & strCell

    The result of VLookup (for example "Z1") will be assigned to strCell, and the formula of the target cell will be set to "=" followed by this result, e.g. "=Z1".

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup return value (2000+)

    Thanx HansV-- I need VBA and just couldn't figure out how to us "indirect" which I have no learnt does not exist in VBA.
    Regards
    Smbs

Posting Permissions

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