how to search all w sh in WB

[rrosa1]

hi
i found this code when i Google for my problem it work good but i need it
change the code to search all work sh in this wb since there may be more wsheet will be in my wb.
here i try to change some code as

[vba]For Each Wks In ThisWorkbook.Worksheets

'''from code
Next Wks[/vba]
but it give " run time error 35600 Index out of bound"
pl help me
thanks for looking my thread.

[Simon Lloyd]

Really when posting code you should post it all rather than force us to download the example and search to find your code and what you mean!

That said, your code looks like this[VBA]For Each Wks In ThisWorkbook.Worksheets
Set Wks = Sheets(1)[/VBA]so its been resticted to one sheet, change it to this[VBA]For Each Wks In ThisWorkbook.Worksheets
[/VBA]Now it will work for all sheets as per your request.

[rrosa1]

sorry for inconvenience Simon

but still it give the same error msg

and it stuck at following code in red font

[vba]Private Sub CommandButton1_Click()
'SEARCH

Dim Cnt As Long
Dim Col As Variant
Dim FirstAddx As String
Dim FoundMatch As Range
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim Wks As Worksheet

StartRow = 6
For Each Wks In ThisWorkbook.Worksheets
' Set Wks = Sheets(1)

Col = ComboBox1.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a category."
Exit Sub
End If

If TextBox1.Text = "" Then
MsgBox "Please enter a search term."
TextBox1.SetFocus
Exit Sub
End If

LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)

Set Rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))

Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
After:=Rng.Cells(1, 1), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'LookAt:=xlWhole,
If Not FoundMatch Is Nothing Then
FirstAddx = FoundMatch.Address
ListView1.ListItems.Clear

Do
Cnt = Cnt + 1
R = FoundMatch.Row
ListView1.ListItems.Add Index:=Cnt, Text:=R
For Col = 1 To 13
Set C = Wks.Cells(R, Col)
ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=C.Text 'it stuck here
Next Col
Set FoundMatch = Rng.FindNext(FoundMatch)
Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
SearchRecords = Cnt
Label4.Caption = "Records Matched =" & Str(Cnt)
Else
ListView1.ListItems.Clear
Label4.Caption = ""
SearchRecords = 0
MsgBox "No match found for " & TextBox1.Text
End If

Next Wks

End Sub

Private Sub UserForm_Activate()

Dim C As Long
Dim i As Long
Dim R As Long
Dim Wks As Worksheet

With ListView1
.Gridlines = True
.View = lvwReport
.HideSelection = False
.FullRowSelect = True
.HotTracking = True
.HoverSelection = False
.ColumnHeaders.Add Text:="Row", Width:=35
End With

'Set Wks = Sheets(1)
For Each Wks In ThisWorkbook.Worksheets
For C = 1 To 13
ListView1.ColumnHeaders.Add Text:=Wks.Cells(3, C).Text, Width:=50
ComboBox1.AddItem Wks.Cells(3, C).Text
Next C
Next Wks
End Sub[/vba]

[Simon Lloyd]

I've not checked again but have you added a message box just before ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=C.Text 'it stuck here to show that there is a value for Col and C?

[rrosa1]

hi simon
i did not add msg box. it the code where it stuck the execution.i just color the font and add comment so u find it where the code stuck.

[Simon Lloyd]

I guess you missed the point add[VBA]msgbox "Count is " & cnt &vblf & "Col is " & col & vblf & "c is " & c[/VBA]it should then give you the values of those things so you can check whether you have the correct values for what you're trying to do.

[rrosa1]

hi simon
thanks for help
but still the code do not search the next sheet and give me the error msg
Index out of Bound. here i need to search all work sheet for perticula value in respective column.

[Simon Lloyd]

What happened with the msgbox? did it give you values? were they the correct ones?

[rrosa1]

hi simon
the msg box give the value of
first time
count is 1
col is 11
c is
then press OK button it gives
count is 2
col is 11
c is D

then press OK button it gives
count is 3
col is 11
c is D
till last count of first sh then it fire error msg
"Run time error '35600'
Index out of bounds
and highlight the code line in red font in following code

[vba]Do
Cnt = Cnt + 1
r = FoundMatch.Row
ListView1.ListItems.Add Index:=Cnt, Text:=r
MsgBox "Count is " & Cnt & vbLf & "Col is " & Col & vbLf & "c is " & c
For Col = 1 To 10
Set c = Cells(r, Col)
ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=c.Text
Next Col
Set FoundMatch = rng.FindNext(FoundMatch)
Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing[/vba]

thanks for bear with me i am not pro with this