Results 1 to 9 of 9

Thread: VBA VLookup or Match Function to return a Row instead of Single Cell Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Hi Community,

    I have a field for users to search for an employee in Cell "B24". What I want to happen is that when a user types in the name of an employee and clicks the 'Enter' button on the keyboard to save what was entered that my sub will run a VLookup or match function to return a row of data for the corresponding Employee from my "Data" sheet. Additionally, I need the vLookup or Match function to return each Column value (A2:X2) from the data sheet for the corresponding employee. and display it on the ActiveSheet starting at Range("A30"). MY code is below and any help would be appreciated:

    Code:
    Public Sub Auto_GetData()
    
    Dim DataVal As String
    DataVal = Range("B24")
    ' DataDisplay = Range("A30:X30")
    
    
    DataOut = Application.WorksheetFunction.VLookup(DataVal, Worksheets("Data").Range("A2:X54"), 1 + 1, False)
    
    
    Range("A30") = DataOut
    
    
    
    
    End Sub
    Last edited by bemp87; Aug 24th, 2019 at 04:39 PM.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Why not just have VLOOKUP (or easier INDEX/MATCH) formulas in A30:X30?
    For example, in A30 copied across to X30

    =IF($B$24="","",INDEX(Data!A$2:A$54,MATCH($B$24,Data!$A$2:$A$54,0)))
    Last edited by Peter_SSs; Aug 24th, 2019 at 08:19 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Hi Peter,

    Thanks for the response. This solution has been tested and it does work just not in the way that we need, it is preferred that this gets coded in a vba macro to avoid having the formula tampered with, and we have a couple other operations that could show different data in this same range. In short, we have different macros attached to different buttons, and depending on the button we click, it can show a different data set in the same range if that makes sense, and that is why we are trying to code as vba. Any additional help would be appreciated

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    How about
    Code:
    Sub bemp87()
       Dim Fnd As Range
       
       Set Fnd = Sheets("pcode").Range("A:A").Find(Range("B24").Value, , , xlWhole, , , False, , False)
       If Not Fnd Is Nothing Then
          Range("A30:X30").Value = Fnd.Resize(, 25).Value
       Else
          MsgBox "Not found"
       End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Thank you the code is working peffect, but I have one more question. In order for this to run I am using the following code to run a Macro after i enter in the search value in cell "B24" and then ir will run the macro Bemp87()- but it's not working, any guidance on why it won't run, it will only run if i attach bemp87 to a button or run it manully

    Worksheet vba code: that will run bemp87 after i enter a value in cell B24
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$B24" Then Application.Run "bemp87"
    End Sub
    bemp87 macro:
    Code:
    Sub bemp87()   Dim Fnd As Range
       
       Set Fnd = Sheets("Data").Range("A:A").Find(Range("B24").Value, , , xlWhole, , , False, , False)
       If Not Fnd Is Nothing Then
          Range("A30:X30").Value = Fnd.Resize(, 25).Value
       Else
          MsgBox "Not found"
       End If
    End Sub
    Last edited by bemp87; Aug 26th, 2019 at 03:27 PM.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Try
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim Fnd As Range
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "B24" Then
          Set Fnd = Sheets("Data").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
          If Not Fnd Is Nothing Then
             Range("A30:X30").Value = Fnd.Resize(, 25).Value
          Else
             MsgBox "Not found"
          End If
       End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Quote Originally Posted by bemp87 View Post
    any guidance on why it won't run,

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Address = "$B24" Then Application.Run "bemp87"
    End Sub
    Yes, that address would need to be $B$24
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Dec 2016
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Hi Community,

    Thanks for all the help. The following ciode works perfectly, but there is one additional question i'm hoping you can help me solve:

    This works perfectly if it is finding one single row that meets the value, but what if there are multiple rows that meets the search condition, how can i modify the code to return all rows?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)   Dim Fnd As Range
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "B24" Then
          Set Fnd = Sheets("Data").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
          If Not Fnd Is Nothing Then
             Range("A30:X30").Value = Fnd.Resize(, 25).Value
          Else
             MsgBox "Not found"
          End If
       End If End Sub

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA VLookup or Match Function to return a Row instead of Single Cell Value

    Can you be more specific about where the data being searched is?

    1. Is row 1 a heading row?
    2. Does the actual data start in row 2?
    3. Where does the actual data end? I'm assuming somewhere above row 24 since that is where your search box is.
    4. Can you confirm that the data occupies columns A:X?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •