Using VBA code when searching in a spreadsheet
 

I have a form in an excel spreadsheet, with two textboxes and one comb
box. I have below code, which will search for values
given in the combobox.

Private Sub CommandButton1_Click()

Dim foundcell As Object
myvalue = ComboBox1.Value
Set foundcell = ActiveSheet.Columns(1).Find(myvalue)
If foundcell Is Nothing Then
MsgBox ("Not found.")
Else
foundcell.Select
End If


End Sub

If e.g. the comobox1 is empty, it should look for a value in textbox1
and search for that in column(2) in the spreadsheet, and if that als
is empty, it should look for the value in textbox2, and search for tha
in column(3) in the spreadsheet.

Help is badly needed. Thnks

--
Message posted from http://www.ExcelForum.com

Using VBA code when searching in a spreadsheet
 

if me.combobox1.text ="" then
myvalue = me.textbox1.text
else
myvalue = me.combobox1.text
end if

The above would have to be used in the userform's module. If you use the
userform name instead of "me",then you can use the above code in a regular
module.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"anb001 " wrote in message
...
I have a form in an excel spreadsheet, with two textboxes and one combo
box. I have below code, which will search for values
given in the combobox.

Private Sub CommandButton1_Click()

Dim foundcell As Object
myvalue = ComboBox1.Value
Set foundcell = ActiveSheet.Columns(1).Find(myvalue)
If foundcell Is Nothing Then
MsgBox ("Not found.")
Else
foundcell.Select
End If


End Sub

If e.g. the comobox1 is empty, it should look for a value in textbox1,
and search for that in column(2) in the spreadsheet, and if that also
is empty, it should look for the value in textbox2, and search for that
in column(3) in the spreadsheet.

Help is badly needed. Thnks.


---
Message posted from http://www.ExcelForum.com/

Using VBA code when searching in a spreadsheet
 

Try this :-



Code
-------------------
Private Sub CommandButton1_Click()
Dim foundcell As Object
Dim Col As Integer
'-----------------------------
If ComboBox1.Value < "" Then
myvalue = ComboBox1.Value
Col = 1
ElseIf ComboBox2.Value < "" Then
myvalue = ComboBox2.Value
Col = 2
ElseIf ComboBox3.Value < "" Then
myvalue = ComboBox3.Value
Col = 3
Else
MsgBox ("No entry made")
Exit Sub
End If
'--------------------------
Set foundcell = ActiveSheet.Columns(Col).Find(myvalue)
If foundcell Is Nothing Then
MsgBox ("Not found.")
Else
foundcell.Select
End If
End Sub

-------------------


--
Message posted from http://www.ExcelForum.com

Using VBA code when searching in a spreadsheet
 

Excellent.
Thanks

--
Message posted from http://www.ExcelForum.com