VBA Search for matching rows in another sheet & display all data in a table

[dramqueenuk]

I have a sheet called "Overview". This sheet is where the user will enter their agency ID (in a cell named "Agency_ID_Search").

I want to search for all rows that use that same agency ID in the sheet called "Dashboard".

I want to pick out the data that's in column A (user ID), column I (day 1 Date), column M (VNHO Assigned Date), column P (VNHO Completion Date), and column O (Training Record Status).

I want to display the data in this table that's on the "Overview" sheet:

I would know how to do this if I just wanted to return the first match, but I'm unsure how to get it to return all data.

 

[offthelip]

Your requirements are not quite clear, so i made some assumptions:
1: I assumed that the Agency_ID given is to be matched with the USER ID in column A on the dashboard sheet
2: You haven't specified where the table is on the overview sheet where you want the results so I assumed it started at A18
3: I assumed that "in a cell named Agency_ID_Search" meant that this was a named range
try this code:

Sub test()
Dim outarr()
With Worksheets("Dashboard")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = Range(.Cells(1, 1), Cells(lastrow, 16)) ' load columns A to P and 1 to last row into a variant array
End With

ReDim outarr(1 To lastrow, 1 To 5) ' declare output array size

With Worksheets("Overview")
ID = Range("Agency_id")

indi = 1 ' initialise output  row
For i = 1 To lastrow
   If ID = datar(i, 1) Then ' match found copy row to output
    outarr(indi, 1) = datar(i, 1)
    outarr(indi, 2) = datar(i, 9)
    outarr(indi, 3) = datar(i, 13)
    outarr(indi, 4) = datar(i, 16)
    outarr(indi, 5) = datar(i, 15)
    indi = indi + 1
   End If
Next i
   Range(.Cells(18, 1), Cells(18 + indi, 16)) = outarr ' write the output array to the worksheet
End With
End Sub

 

[dramqueenuk]

Hi offthelip. I tried your code with a couple of tweaks:
1. The dashboard goes to column U, not P, so I changed "16" to "21" in the 'lastrow' line.
2. I changed the range "Agency_ID" to "Agency_Search" as that what it's named
3. The table for the output starts at B18, not A18, so I amended the output columns.

That means the code now shows:

Sub Ageny_Search()
Dim outarr()
With Worksheets("Dashboard")
lastrow = .Cells(Rows.count, "A").End(xlUp).Row
datar = Range(.Cells(1, 1), Cells(lastrow, 21))
End With

ReDim outarr(1 To lastrow, 2 To 6)

With Worksheets("Overview")
ID = Range("Agency_Search")

indi = 1
For i = 1 To lastrow
   If ID = datar(i, 1) Then
    outarr(indi, 2) = datar(i, 1)
    outarr(indi, 3) = datar(i, 9)
    outarr(indi, 4) = datar(i, 13)
    outarr(indi, 5) = datar(i, 16)
    outarr(indi, 6) = datar(i, 15)
    indi = indi + 1
   End If
Next i
   Range(.Cells(18, 1), Cells(18 + indi, 21)) = outarr
End With
End Sub

When I try and run it, I get the error:
Run time error '1004':
Method 'Range' of object '_Global' failed

And it highlights the last row line.
If I change that line to replace "A" with 1, it passes that row and highlights the same error in the last 'Range' line:
Range(.Cells(18, 2), Cells(18 + indi, 21)) = outarr

Any ideas?

 

[dramqueenuk]

As the 'Agency_Search' is the name of an agency, there's scope for it to go wrong if they type an extra space, so is there a way to add a TRIM to it?

Plus, they could write the name in a different case from what's in the Dashboard so is there a way to tell it to ignore the case?

 

[dramqueenuk]

I've realised that it will be better for the user to be able to search for data using a range of search criteria.

There will be 6 fields the user can update:
1. Agency name (field is named "Agency_Search")
2. Date Range (from date is in the field called "From_Date" and to date is in the field called "To_Date")
3. User ID (field is named "User_ID_Search")
4. User's surname (field is named "User_Surname")
5. User's first name (field is named "User_First_Name")

Once they've entered their search criteria, they click on 'Search'. This will be the trigger for the VBA code. The search criteria is all on the sheet called, "Overview". Note that the free text fields (agency name, user surname, and user first name) would need a TRIM or something to ensure that there was no extra spacing preventing a match, and it would need to ignore the case used, i.e. capitals or lower case.

I want it to search the sheet called, "Dashboard" for rows that match the search criteria. There might be one or more results.

From the results, I want it to place certain pieces of information in a table that's in the "Overview" sheet.

Initially, I was going to do each search option as a separate search button so I tried this code to just search for the user ID. However, it's not working - the error is on the 'Fnd.Offset' row - and I've now decided that it's better to have all the search criteria as one search. Any ideas how I can edit the code to a) make it work and b) include the other search criteria? Or any tutorials or anything you can direct me to?

Sub SearchUser()
Dim Fnd As Range

Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Off").Value, , , xlWhole, , , False, , False)
If Fnd Is Nothing Then
MsgBox Range("User_ID_Search").Value & " not found"
Exit Sub
End If

Fnd.Offset(Sheets("Dashboard").Cells(x, 1) = Sheets("Overview").Range("User_ID_Search")
Sheets("Overview").Range("B18") = Sheets("Dashboard").Cells(x, 1)
Sheets("Overview").Range("C18") = Sheets("Dashboard").Cells(x, 9)
Sheets("Overview").Range("D18") = Sheets("Dashboard").Cells(x, 13)
Sheets("Overview").Range("E18") = Sheets("Dashboard").Cells(x, 16)
Sheets("Overview").Range("F18") = Sheets("Dashboard").Cells(x, 15)

Else
response = MsgBox("User not found", vbExclamation)
  If response = vbOK Then
  Exit Sub
  End If
End If

End Sub