Your understanding (and code) appears to be correct. No offense, but
based on my own embarrassing experiences, perhaps your eyes are playing
a trick on you - maybe with spaces vs. underscores or such.

To slay this beast it won't hurt (during development) to add this just
before your Match:
debug.print "<" & activesheet.name & "" ' use some unique delimiter

This kind of thing may be useful on something down the road anyway; from
time to time it shows me I need to trim something.

On Wed, 20 Aug 2003 11:37:28 +0100, "Scott" wrote:

Hi,

I'm having a little problem with this code and I haven't a clue what the
problem is.
This code checks to see if the active sheet is listed in the array, if it is
then it exits the sub.

My problem is that this works fine in a new workbook, even if the case is
different. But the workbook in which I try to run this code works fine if
the case is the same, but if the case is different the sheet is not found
and the massage "Sheet Not Found" is displayed. I don't think there's
anything special about this workbook, so this is really confusing me.

Has anyone got any ideas to what the problem is?

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
If Not IsError(res) Then
Msgbox "Sheet Found"
Exit Sub

End If

Msgbox "Sheet Not Found"

Hi,

Thanks for your reply,
I have checked to make sure that there is no typo, but still having
problems.
I've typed in the debug code you suggested like below, but not sure if this
is correct.
Now if the sheet has different case an 'Type Mismatch' error pops up.

Debug.Print "<" & ActiveSheet.Name & ""; res =
Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2", "sheet3"), 0)

Anymore suggestions?
Thanks
Best regards,
Scott



"Wild Bill" wrote in message
...
Your understanding (and code) appears to be correct. No offense, but
based on my own embarrassing experiences, perhaps your eyes are playing
a trick on you - maybe with spaces vs. underscores or such.

To slay this beast it won't hurt (during development) to add this just
before your Match:
debug.print "<" & activesheet.name & "" ' use some unique delimiter

This kind of thing may be useful on something down the road anyway; from
time to time it shows me I need to trim something.

On Wed, 20 Aug 2003 11:37:28 +0100, "Scott" wrote:

Hi,

I'm having a little problem with this code and I haven't a clue what the
problem is.
This code checks to see if the active sheet is listed in the array, if it
is
then it exits the sub.

My problem is that this works fine in a new workbook, even if the case is
different. But the workbook in which I try to run this code works fine if
the case is the same, but if the case is different the sheet is not found
and the massage "Sheet Not Found" is displayed. I don't think there's
anything special about this workbook, so this is really confusing me.

Has anyone got any ideas to what the problem is?

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
If Not IsError(res) Then
Msgbox "Sheet Found"
Exit Sub

End If

Msgbox "Sheet Not Found"

you would leave the res = out of your debug statement.

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
if iserror(res) then
Debug.Print "<" & ActiveSheet.Name & ""
End if

--
Regards,
Tom Ogilvy




"Scott" wrote in message
...
Hi,

Thanks for your reply,
I have checked to make sure that there is no typo, but still having
problems.
I've typed in the debug code you suggested like below, but not sure if
this
is correct.
Now if the sheet has different case an 'Type Mismatch' error pops up.

Debug.Print "<" & ActiveSheet.Name & ""; res =
Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2", "sheet3"),
0)

Anymore suggestions?
Thanks
Best regards,
Scott



"Wild Bill" wrote in message
...
Your understanding (and code) appears to be correct. No offense, but
based on my own embarrassing experiences, perhaps your eyes are playing
a trick on you - maybe with spaces vs. underscores or such.

To slay this beast it won't hurt (during development) to add this just
before your Match:
debug.print "<" & activesheet.name & "" ' use some unique delimiter

This kind of thing may be useful on something down the road anyway; from
time to time it shows me I need to trim something.

On Wed, 20 Aug 2003 11:37:28 +0100, "Scott" wrote:

Hi,

I'm having a little problem with this code and I haven't a clue what the
problem is.
This code checks to see if the active sheet is listed in the array, if it
is
then it exits the sub.

My problem is that this works fine in a new workbook, even if the case is
different. But the workbook in which I try to run this code works fine if
the case is the same, but if the case is different the sheet is not found
and the massage "Sheet Not Found" is displayed. I don't think there's
anything special about this workbook, so this is really confusing me.

Has anyone got any ideas to what the problem is?

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
If Not IsError(res) Then
Msgbox "Sheet Found"
Exit Sub

End If

Msgbox "Sheet Not Found"

Bill provides some good advice.

Heres is some code you can test with:

Sub tester1()
Dim i As Long, myarray As Variant
myarray = Array("sheet1", "sheet2", "sheet3")
For i = LBound(myarray) To UBound(myarray)
res = StrComp(ActiveSheet.Name, myarray(i), vbTextCompare)
If res = 0 Then
MsgBox "Match: " & ActiveSheet.Name & " is in array at index " & i _
& " whose value is " & myarray(i)
Else
MsgBox "No Match: -" & ActiveSheet.Name & "<=" & _
myarray(i) & "<- at index: " & i
End If
Next
End Sub

--
Regards,
Tom Ogilvy



"Wild Bill" wrote in message
...
Your understanding (and code) appears to be correct. No offense, but
based on my own embarrassing experiences, perhaps your eyes are playing
a trick on you - maybe with spaces vs. underscores or such.

To slay this beast it won't hurt (during development) to add this just
before your Match:
debug.print "<" & activesheet.name & "" ' use some unique delimiter

This kind of thing may be useful on something down the road anyway; from
time to time it shows me I need to trim something.

On Wed, 20 Aug 2003 11:37:28 +0100, "Scott" wrote:

Hi,

I'm having a little problem with this code and I haven't a clue what the
problem is.
This code checks to see if the active sheet is listed in the array, if it
is
then it exits the sub.

My problem is that this works fine in a new workbook, even if the case is
different. But the workbook in which I try to run this code works fine if
the case is the same, but if the case is different the sheet is not found
and the massage "Sheet Not Found" is displayed. I don't think there's
anything special about this workbook, so this is really confusing me.

Has anyone got any ideas to what the problem is?

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
If Not IsError(res) Then
Msgbox "Sheet Found"
Exit Sub

End If

Msgbox "Sheet Not Found"

Thanks Tom, this works, but why does this work and not the other code?
Are you able to explain to a newbie?

Thanks
Best regards,
Scott


"Tom Ogilvy" wrote in message
...
Bill provides some good advice.

Heres is some code you can test with:

Sub tester1()
Dim i As Long, myarray As Variant
myarray = Array("sheet1", "sheet2", "sheet3")
For i = LBound(myarray) To UBound(myarray)
res = StrComp(ActiveSheet.Name, myarray(i), vbTextCompare)
If res = 0 Then
MsgBox "Match: " & ActiveSheet.Name & " is in array at index " & i _
& " whose value is " & myarray(i)
Else
MsgBox "No Match: -" & ActiveSheet.Name & "<=" & _
myarray(i) & "<- at index: " & i
End If
Next
End Sub

--
Regards,
Tom Ogilvy



"Wild Bill" wrote in message
...
Your understanding (and code) appears to be correct. No offense, but
based on my own embarrassing experiences, perhaps your eyes are playing
a trick on you - maybe with spaces vs. underscores or such.

To slay this beast it won't hurt (during development) to add this just
before your Match:
debug.print "<" & activesheet.name & "" ' use some unique delimiter

This kind of thing may be useful on something down the road anyway; from
time to time it shows me I need to trim something.

On Wed, 20 Aug 2003 11:37:28 +0100, "Scott" wrote:

Hi,

I'm having a little problem with this code and I haven't a clue what the
problem is.
This code checks to see if the active sheet is listed in the array, if it
is
then it exits the sub.

My problem is that this works fine in a new workbook, even if the case is
different. But the workbook in which I try to run this code works fine if
the case is the same, but if the case is different the sheet is not found
and the massage "Sheet Not Found" is displayed. I don't think there's
anything special about this workbook, so this is really confusing me.

Has anyone got any ideas to what the problem is?

res = Application.Match(ActiveSheet.Name, Array("sheet1", "sheet2",
"sheet3"), 0)
If Not IsError(res) Then
Msgbox "Sheet Found"
Exit Sub

End If

Msgbox "Sheet Not Found"

Your understanding of the code sounds fine, and your code seemed fine.
So in cases like this you sometimes want to step back and "think out of
the box" to try and find a flaw in your basic premises - such as whether
there's an invisible blank space in a string you're looking at.

A useful next step for both newbies and experts - and usually very
quickly rewarding - is to debug, taking advantage of the mouse flyover,
debug.print, watch, breakpoints, etc. in the VBA IDE. Becoming
proficient and efficient with this, making it a minimal distraction
while you remain focused on bigger issues, is worthwhile.

So why didn't your code work for you? Who knows. Yet very possibly,
and again I mean no offense, it's something of the order of "Oh, I
thought that variable contained..." or "Oh I didn't know there was a
space in that cell," etc. My advice is to attack every variable, attack
every assumption you have, etc., as far as you can. (Which no doubt you
did)

Then come here :) (Which you did)

On Wed, 20 Aug 2003 17:13:24 +0100, "Scott" wrote:

Thanks Tom, this works, but why does this work and not the other code?
Are you able to explain to a newbie?