Better Fix For Endless Do While Statement

[RShow]

I had to add the IF statement below to stop the Loop if MyValue wasn't found
on any of the
sheets. Is there any better way to do the following, or since this seems to
work, just leave it alone? workbook has only 8 sheets.
Thanks.

the code:

MyValue = PO_Number

Dim MyVar
On Error Resume Next
a = 1
Do While MyVar = ""
MyVar = Application.WorksheetFunction _
.Match(MyValue, Worksheets(a).Range("E1:E3000"), 0)

a = a + 1

If a = 8 And MyVar = "" Then
MsgBox ("PO # Not Found In Records.")
Exit Sub
End If

Loop
' Select sheet and cell after finding
Sheets(Worksheets(a - 1).Name).Select
Range("E" & MyVar).Select

 

[RB Smissaert]

Haven't tested, but using Find is probably faster:

Sub test()

Dim strText As String
Dim sh As Worksheet
Dim c As Range

strText = "test"

For Each sh In ThisWorkbook.Worksheets
With Range(sh.Cells(5), sh.Cells(3000, 5)).Cells
Set c = .Find(strText, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
sh.Activate
c.Select
Exit Sub
End If
End With
Next

If c Is Nothing Then
MsgBox "could't find " & strText
End If

End Sub


RBS

 

[RShow]

Thanks
That does the trick. Works Great!

Haven't tested, but using Find is probably faster:

Sub test()

Dim strText As String
Dim sh As Worksheet
Dim c As Range

strText = "test"

For Each sh In ThisWorkbook.Worksheets
With Range(sh.Cells(5), sh.Cells(3000, 5)).Cells
Set c = .Find(strText, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
sh.Activate
c.Select
Exit Sub
End If
End With
Next

If c Is Nothing Then
MsgBox "could't find " & strText
End If

End Sub


RBS