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

Hello Otto,

Have you tried starting at the same location, say A1, after you select the
two sheets?

HTH, Greg

"Otto Moehrbach" wrote in message
...
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

Greg
Thanks for your reply. I don't know what you mean by "starting at the
same location". I tried putting [A1].Select after the two pages were
selected but that didn't help. Otto
"Greg Koppel" wrote in message
...
Hello Otto,

Have you tried starting at the same location, say A1, after you select the
two sheets?

HTH, Greg

"Otto Moehrbach" wrote in message
...
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

I would try searching each sheet separately:

Sub Test()
Dim FoundCell As Range
SearchPart = Sheets("Enter Data").Range("D32").Value
With Worksheets("Air Compressor")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With

if FoundCell is nothing then
With Worksheets("Bay Door")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With
End If
if Not FoundCell is Nothing then
MsgBox FoundCell.Address(external:=True)
Else
MsgBox "Target was not found"
End If
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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

Tom
I appreciate your help. I'll give it a try. The macro I posted is an
abbreviation of the whole thing I have to demonstrate my problem. The code
will have already set up an array of some 18 sheets. I'll try what you
suggest and just use a For loop to loop through the sheets in the array
rather than select the entire array as I have been doing. Something like:
For i = 1 To ShtArray.Count
With Worksheets(ShtArray(i))
Do you see anything wrong with that?
Thanks again. Otto
"Tom Ogilvy" wrote in message
...
I would try searching each sheet separately:

Sub Test()
Dim FoundCell As Range
SearchPart = Sheets("Enter Data").Range("D32").Value
With Worksheets("Air Compressor")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With

if FoundCell is nothing then
With Worksheets("Bay Door")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With
End If
if Not FoundCell is Nothing then
MsgBox FoundCell.Address(external:=True)
Else
MsgBox "Target was not found"
End If
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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

Tom
Thanks again. I massaged what you gave me and fit it into my code and
it works like a charm. I won't try to search grouped sheets by code again.
Otto
"Tom Ogilvy" wrote in message
...
I would try searching each sheet separately:

Sub Test()
Dim FoundCell As Range
SearchPart = Sheets("Enter Data").Range("D32").Value
With Worksheets("Air Compressor")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With

if FoundCell is nothing then
With Worksheets("Bay Door")
Set FoundCell = .Columns(F).Find(What:=SearchPart, _
After:=.Range("F65536"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With
End If
if Not FoundCell is Nothing then
MsgBox FoundCell.Address(external:=True)
Else
MsgBox "Target was not found"
End If
End Sub

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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