Search Functionality

[MJatAflac]

This is my first attempt at using VBA in Excel so be gentle if this is a
stupid question.

I have a user form with a combobox that gets it's values from a range on a
different sheet. What I need to do is let the user click a button and return
the entire row where the data chosen in the combobox was found.

Can anyone give me any pointer on this?

I do appreciate it!

 

[JLGWhiz]

The only stupid question is the one not asked.

This will provide the basic elements of code. The
Code goes behind the ComboBox by right clicking the
control and click "View Code" The First and last
command lines should automatically appear so you
just have to fill in between them.

Private Sub ComboBox1_Click()
Dim c As Range, myVar As Variant
myVar = ComboBox1.Value
Set SearchRange = Worksheets(2).Range("F2:F250")
Set c = SearchRange.Find(myVar, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Copy 'Destination Range goes here
End If
End Sub

 

[JLGWhiz]

Some additional comments:

The search range used in the previously posted code is an arbitrary range.
I assumed that you would know where the source range for your combobox was
located and that would be the range used in the code for the search range.
If you don't know where the range is located, the the search would have to be
done on the entire sheet and you would eliminate the range part of the search
variable and substitute Cells, as follows:

Set SearchRange = Worksheets(2).Cells

The problem with this is, that you could have the search criteria (combobox
value) in more than one place on the sheet and you might not get the right
row. So, you need to plan your code development based on the data base you
are working with. Contrary to popular beilief, computers are stupid. Their
value lies in their speed, not their genius.

 

[JLGWhiz]

The information at this site might be even more useful to you in
understanding how to use the combo box and list box controls. They have just
revamped this site. The main page for this site allows you to start from
scratch and learn the fundamentals of VBA. It is a MS sponsored site.

http://www.excel-vba.com/vba-24d-combo-boxes.htm

 

[MJatAflac]

Thank you very much for your help! I really appreciate it.