+ Reply to Thread
Results 1 to 11 of 11

Using target in VLookUp formula doesn't work

Hybrid View

  1. 07-02-2013, 03:31 PM #1
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,027

    Using target in VLookUp formula doesn't work

    I am trying to insert a formula into a cell but it is not working. Any help would be very much appreciated. This is the code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Cells(Target.Row, 5).Formula = "=vlookup(target,Sheet2!$A$5:$B$30,2,FALSE)"
End Sub
    Reply With QuoteRegister To Reply
  2. 07-02-2013, 03:35 PM #2
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using target in VLookUp formula doesn't work

    Target, as you are using it, will return the value of the cell.
    VLOOKUP expects this to be a cell address.

    Try using:
    Target.Address
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
    Reply With QuoteRegister To Reply
  3. 07-02-2013, 03:36 PM #3
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Using target in VLookUp formula doesn't work 

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Cells(Target.Row, 5).Formula = "=vlookup(" & Target.Address & ",Sheet2!$A$5:$B$30,2,FALSE)"
End Sub
    Reply With QuoteRegister To Reply
  4. 07-02-2013, 03:37 PM #4
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Using target in VLookUp formula doesn't work

    Hi, Mumps1,

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Cells(Target.Row, 5).Formula = "=vlookup(" & target.address &",Sheet2!$A$5:$B$30,2,FALSE)"
End Sub
    Ciao,
    Holger
    Last edited by HaHoBe; 07-02-2013 at 03:38 PM. Reason: sorry for late post - didnīt see the last post in preview
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  5. 07-02-2013, 03:37 PM #5
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Using target in VLookUp formula doesn't work

    Its not working as in the cell isn't populated with the formula? (works find for me) or your formula isn't working?
    Reply With QuoteRegister To Reply
  6. 07-02-2013, 03:40 PM #6
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Using target in VLookUp formula doesn't work

    Holger,

    I must be blind, I'm scanning but I can't tell what you've changed.

    Edit:

    Sorry. Got it.
    Reply With QuoteRegister To Reply
  7. 07-02-2013, 03:42 PM #7
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Using target in VLookUp formula doesn't work

    Hi, XeRo Solus,

    I think I donīt write Target as a string into the cell.

    Ciao,
    Holger
    Reply With QuoteRegister To Reply
  8. 07-02-2013, 03:44 PM #8
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Using target in VLookUp formula doesn't work

    It took me a good 10 minutes to realize I was comparing your post to stnkynts' post and not the OP. Wow...
    Reply With QuoteRegister To Reply
  9. 07-02-2013, 04:06 PM #9
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,027

    Re: Using target in VLookUp formula doesn't work

    Thank you so much to everyone. The suggestions from Holger and stnkynts worked perfectly. Much appreciated.
    Reply With QuoteRegister To Reply
  10. 07-02-2013, 05:04 PM #10
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Using target in VLookUp formula doesn't work

    Hi, Mumps1,

    please mark the thread as Solved via Thread Tools - thank you.

    Holger
    Reply With QuoteRegister To Reply
  11. 07-02-2013, 05:19 PM #11
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,027

    Re: Using target in VLookUp formula doesn't work

    My apologies, Holger. Done!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1