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

[bemp87]

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:

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

 

[Peter_SSs]

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)))

 

[bemp87]

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

 

[Fluff]

How about

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

 

[bemp87]

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

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$B24" Then Application.Run "bemp87"
End Sub

bemp87 macro:

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

 

[Fluff]

Try

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

 

[Peter_SSs]

any guidance on why it won't run,

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

 

[bemp87]

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?

[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]

 

[Peter_SSs]

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?