Excel 2003, WinXP
I have been battling this one all day, trying to find a pattern to the
behavior of this macro. I can't. It appears to be completely random.
I have two sheets.
I take all of the Column F data from each sheet and formulate a list. That
list is in the Data Validation [D32] in the Enter Data sheet.
I select one item in D32. That item now remains constant.
The following macro simply searches the two sheets from which the list was
taken, for [D32] of the Enter Data sheet.
The problem: Sometimes it finds the item. Sometimes it doesn't.
I have tried every which way to find a pattern in the macro behavior but
couldn't. Sometimes it finds it in alternating executions of the macro.
Other times it doesn't. The error highlight is, of course, in the MsgBox
line.
Does anyone have an idea of what I am doing wrong? Thanks for your help.
Otto
Sub Test()
Dim FoundCell As Range
SearchPart = Sheets("Enter Data").Range("D32").Value
Sheets(Array("Air Compressor", "Bay Door")).Select
Set FoundCell = Selection.Find(What:=SearchPart, _
After:=Selection(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True)
MsgBox FoundCell.Row
Sheets("Enter Data").Select
[D32].Select
End Sub