Search in Range
 

Hi All,

I have a workbook with several sheets.
Column (J2:J65536.end(xlup))is named as RecNo except in
the first two and last two sheets.
I want to search all sheets except the first two sheets
and last two sheets for a value in combobox1 in userform1
and if found i want to add the sheet name (s) in listbox1

How can I accoplish this

Thanks in advance
Soniya

Search in Range
 

Hi Soniya,

Try this code

Dim cell As Range
Dim rng As Range
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
Set rng = sh.Range("RecNo")
If Not rng Is Nothing Then
Set cell = sh.Cells.Find(ComboBox1.Value)
If Not cell Is Nothing Then
Me.ListBox1.AddItem sh.Name
End If
End If
Set rng = Nothing
Set cell = Nothing
On Error GoTo 0
Next sh
With Me.ListBox1
If .ListCount 0 Then
.ListIndex = 0
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Soniya" wrote in message
...
Hi All,

I have a workbook with several sheets.
Column (J2:J65536.end(xlup))is named as RecNo except in
the first two and last two sheets.
I want to search all sheets except the first two sheets
and last two sheets for a value in combobox1 in userform1
and if found i want to add the sheet name (s) in listbox1

How can I accoplish this

Thanks in advance
Soniya