Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup including the comment (excel 97)

    how to use vlookup function to see the comment in that cell also.
    thanks a lot

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup including the comment (excel 97)

    Vlookup can no see cell Comments, and as far as I know, none of the worksheet functions can.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup including the comment (excel 97)

    Legare Coleman
    thank you for the promptly reply.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup including the comment (excel 97)

    Only using a user defined function:

    Function VlookupWithComment(rLookForRange As Range, rLookInRange As Range, lColumnOffset As Long, bIncludeComment As Boolean)
    Dim vTemp As Variant
    Dim oSheet As Object
    Dim rFoundcell As Range
    Set oSheet = Application.Caller.Parent
    Set rFoundcell = rLookInRange.Cells(Application.WorksheetFunction.M atch(rLookForRange.Value, rLookInRange.Columns(1), 0), lColumnOffset)
    vTemp = rFoundcell.Value
    If bIncludeComment Then
    vTemp = vTemp & ", Comment: " & rFoundcell.Comment.Text
    End If
    VlookupWithComment = vTemp
    End Function

    Paste this VBA code into a normal module and call the function from your worksheet like this:

    =VlookupWithComment(E2,A1:B5,2,TRUE)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup including the comment (excel 97)

    Pieterse
    I am new on user defined fuction. When I copy and save your code into the module, I do not know how to retrive it to edit.
    Joe

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup including the comment (excel 97)

    You press alt-F11 to get into the Visual Basic Editor. Then use the "Project explorer" (normally on the left side of the VBE's window) to navigate to the module that you pasted the code into.

    One remakr: My user defined function yields a string result. If you want to do calculations with the found matches, you'll need to extract the number from the string.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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