Search in Range

[Soniya]

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

 

[Bob Phillips]

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)