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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,536
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,179
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:
Capture 2.PNG

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

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:

Capture.PNG


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

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,179
Office Version
  1. 2019
Platform
  1. Windows
...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

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here's the dropbox link to the file:

Thanks
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,179
Office Version
  1. 2019
Platform
  1. Windows
ok will take a look asap

Dave
 

Mwate

New Member
Joined
May 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,356
Messages
5,547,459
Members
410,793
Latest member
sauravg
Top