Greetings,
I have some code that looks for a specific number, in this case "123456", searches for it thru all worksheets then reports on Sheet1 what worksheet each instance was found.
so Sheet1 would have:
"123456" found in Sheet2
"123456" found in Sheet4 etc..
The problem is once i introduced the code to put the focus in Sheet1 the loop loses it's place and gets stuck on sheet2 in an infinite loop. I try to put the focus back to where it was but the loop isn't fooled. Any suggestions would be helpful.
code below:
thank you
I have some code that looks for a specific number, in this case "123456", searches for it thru all worksheets then reports on Sheet1 what worksheet each instance was found.
so Sheet1 would have:
"123456" found in Sheet2
"123456" found in Sheet4 etc..
The problem is once i introduced the code to put the focus in Sheet1 the loop loses it's place and gets stuck on sheet2 in an infinite loop. I try to put the focus back to where it was but the loop isn't fooled. Any suggestions would be helpful.
code below:
Code:
Sub FindAndExecute()
Dim Sh As Worksheet
Dim Loc As Range
Dim num As String
Dim ShName As String
Ticket = "123456"
For Each Sh In ThisWorkbook.Worksheets
With Sh.UsedRange
Set Loc = .Cells.Find(What:=Ticket)
If Not Loc Is Nothing Then
Do Until Loc Is Nothing
Sh.Activate
'Go to Sheet1 and report ticket-number and sheetname where found
ShName = ActiveSheet.Name
Sheets("Sheet1").Select
L_Row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
Application.ActiveSheet.Cells(L_Row, 1).Value = Ticket & " Found in " & ShName
'Return to previous sheet to keep loop on track
Sh.Activate
Set Loc = .FindNext(Loc)
Loop
End If
End With
Set Loc = Nothing
Next
End Sub
thank you