VBA code to search and display match in another sheet

[Mwate]

Am very new to programming. i have a list of trainees and their corresponding info:

I have then developed an excel user form as follows:

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:

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?

 

[JLGWhiz]

If you are trying to compare the details on the list to the values in the user form you will need to do so by comparing the individual values of the controls on the form to the cell values of each selection on the list. Most likiely you would need to use If...Then statements for each data item. Not sure off hand how to handle the option buttons for sex.

 

[dmt32]

Hi welcome to forum
what you want is doable but as you are new to VBA - share with us the names you have given your controls (and how you are populating the comboboxes) - the sheet name holding the data & then can offer some codes for you to test in your project.

Dave

 

[Mwate]

Hi welcome to forum
what you want is doable but as you are new to VBA - share with us the names you have given your controls (and how you are populating the comboboxes) - the sheet name holding the data & then can offer some codes for you to test in your project.

Dave

Thanks Dave.
I have removed the "Type of output" combobox as i felt its inclusion has no real impact on the output. The newer form looks like this now:

Administrative level entails the district being selected. All districts (29 in total) fall under a specific region (4 regions)
as follows:

Intention is to have both options of either selecting (from the combobox selection) a region and get an output for all corresponding districts and associated entries or to select specific district and get output for all corresponding entries to the selection restricted at district level. My revised database list (in sheet named "Data") is of the following structure, which should also be the structure of the output on a new sheet:

The rank/cadre displayed on the combobox selection is also as indicated in the data list above, where as health facilities combobox provides a choice of the following: Govt Clinic; District Hospital; Private Hospital; or Central Hospital.

Every combobox should allow only one selection. I hope this provides the additional information required. I greatly appreciate the assistance

 

[Mwate]

...and every combobox also needs to have an option of "All" (as a list item). thanks

 

[dmt32]

...and every combobox also needs to have an option of "All" (as a list item). thanks

I take it you do not have code to populate the comboboxes?
Also, I need to know the names you have given to each of the controls (ComboBoxes, OptionButtons, CommandButtons) or have you left them at their default names?
or better, place copy of your workbook in a dropbox & provide a link to it.


Dave

 

[Mwate]

Here's the dropbox link to the file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thanks

 

[Mwate]

Sorry, the above link seems to be corrupted. This one works fine:

Trainees Database - Copy1.xlsm

Shared with Dropbox

www.dropbox.com

Thanks.

 

[dmt32]

ok will take a look asap

Dave

 

[Mwate]

If you are trying to compare the details on the list to the values in the user form you will need to do so by comparing the individual values of the controls on the form to the cell values of each selection on the list. Most likiely you would need to use If...Then statements for each data item. Not sure off hand how to handle the option buttons for sex.

Thanks, sounds straight forward, but its actually quite hard for a beginner to do!! but thanks for the tip, am trying to see if it works for me