VBA code to search and display match in another sheet

Mwate

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Am very new to programming. i have a list of trainees and their corresponding info:

Capture.PNG


I have then developed an excel user form as follows:

Capture 2.PNG

where Admin level = district

I am trying to develop simple database to search list entries using the "extract data" button from multiple selections specified by the combobox selections on the user form.
i have managed to write the code for reseting the form using the "Reset" button and saving the output. am struggling to write a code to search the list using user form selection and return all matching entries into a different worksheet. the following code i have done only returns one entry:

VBA Code:
Sub searchmultiplevalues()
Dim r As Long, c As Long, col_index As Long, lastcol As Long
lastcol = Cells.Find(what:="*", after:=Range("A1"), lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=False).Column

r = 2 
col_index = 2

For c = 2 To lastcol
    Cells(r, c).Value = Application.WorksheetFunction.VLookup(Range("A2"), Range("A10:E20"), col_index, False)
    col_index = col_index + 1
Next c

MsgBox("Search finalized!")

End Sub

The question in summary: how do i perfect this code to take all combobox selections from the user form and display the matching entries (with corresponding information for each matching entry) into another worksheet within the same workbook?
 

Mwate

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Did you manage to come with anything Dave? Thanks
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,180
Office Version
  1. 2019
Platform
  1. Windows
Did you manage to come with anything Dave? Thanks

I have been looking at it but at moment, have a serious family member issue occurring that requires undivided attention.
The code to filter is just one or two lines but the code to manage the lists etc in the form will involve much more - it occurs to me that you could just set the data sheet to autofilter which involves no code & just filter the fields as required? You can then copy the results to another sheet if required.

I will have a play at some stage & let you have anything that I think is credible.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,114,473
Messages
5,548,246
Members
410,824
Latest member
Bobmn4
Top