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

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
102
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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
any guidance on why it won't run,

Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
If Target.Address = "[COLOR="#FF0000"][B]$B24[/B][/COLOR]" Then Application.Run "bemp87"
End Sub
Yes, that address would need to be $B$24
 
Upvote 0
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:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]   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 [COLOR=#333333]End Sub[/COLOR]
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top