checking multiple sheets.
 

Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active sheet
from a list in an array, or something like that, if this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active sheet
from a list in an array, or something like that, if this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active sheet
from a list in an array, or something like that, if this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

There is no change required - it isn't case sensitive as written.

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active
sheet
from a list in an array, or something like that, if this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

to match strings whether they are in upper or lower case
use the option:

Option Compare Text

at the top of the module, before the sub

-----Original Message-----
Thanks Tom,

Just one more thing, is there any way of changing this so
it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array
("sheet1","sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name
of the active sheet
from a list in an array, or something like that, if
this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott





.

checking multiple sheets.
 

Tilde is a special character, so you would have to double up the tilde

?
application.Match("Micro~~100",Array("Micro~10","M icro~50","Micro~100","Micr
o~400"),0)
3

as an example,

--

Regards,
Tom Ogilvy



"Scott" wrote in message
...
Thanks Tom,

I think the Problem is the tilde (~) in the sheet name, it doesn't seem to
recognise it.
the sheet name is something like this, Mirco~100.
Is there any way this can be fixed as the sheet name cannot be renamed.

Hope you can help,
Thanks
Best regards
Scott

"Tom Ogilvy" wrote in message
...
There is no change required - it isn't case sensitive as written.

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active
sheet
from a list in an array, or something like that, if this sheet is in
the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

Hi Tom,

Thank you very much for your help, but I'm still having problems. As you may
have suspected I am only a beginner and don't know how to fix this problem.

I've tried the code you sent, but cannot get it to work on the active sheet.
This code is supposed to check the active sheet, if it is the Micro~100
sheet, then it displays the message "Micro~100 Sheet Found", and if it isn't
it then displays "Sheet Not Found". But it displays "Micro~100 Sheet Found"
weather it's the active sheet or not, can't work it out.

res = Application.Match("Micro~~100", Array("Micro~100"), 0)
If Not IsError(res) Then
Msgbox "Micro~100 Sheet Found"
Exit Sub
End If
Msgbox "Sheet Not Found"

Hope you or someone can please help me out,
Thanks
Best regards,
Scott

P.S. I have another problem with the first bit of code, but I think I'd
better send it in another message.






"Tom Ogilvy" wrote in message
...
Tilde is a special character, so you would have to double up the tilde

?
application.Match("Micro~~100",Array("Micro~10","M icro~50","Micro~100","Micr
o~400"),0)
3

as an example,

--

Regards,
Tom Ogilvy



"Scott" wrote in message
...
Thanks Tom,

I think the Problem is the tilde (~) in the sheet name, it doesn't seem to
recognise it.
the sheet name is something like this, Mirco~100.
Is there any way this can be fixed as the sheet name cannot be renamed.

Hope you can help,
Thanks
Best regards
Scott

"Tom Ogilvy" wrote in message
...
There is no change required - it isn't case sensitive as written.

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active
sheet
from a list in an array, or something like that, if this sheet is in
the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

checking multiple sheets.
 

You've coded the actual names into the line of code. Maybe using the
activesheet's name:

Sub testme01()
Dim res As Variant

res = Application.Match(Application.Substitute(ActiveShe et.Name, "~", "~~"), _
Array("Micro~100"), 0)
If Not IsError(res) Then
MsgBox "Micro~100 Sheet Found"
Exit Sub
End If
MsgBox "Sheet Not Found"
End Sub

If you're using xl2k or above, you can use Replace instead of
application.substitute.

Scott wrote:

Hi Tom,

Thank you very much for your help, but I'm still having problems. As you may
have suspected I am only a beginner and don't know how to fix this problem.

I've tried the code you sent, but cannot get it to work on the active sheet.
This code is supposed to check the active sheet, if it is the Micro~100
sheet, then it displays the message "Micro~100 Sheet Found", and if it isn't
it then displays "Sheet Not Found". But it displays "Micro~100 Sheet Found"
weather it's the active sheet or not, can't work it out.

res = Application.Match("Micro~~100", Array("Micro~100"), 0)
If Not IsError(res) Then
Msgbox "Micro~100 Sheet Found"
Exit Sub
End If
Msgbox "Sheet Not Found"

Hope you or someone can please help me out,
Thanks
Best regards,
Scott

P.S. I have another problem with the first bit of code, but I think I'd
better send it in another message.

"Tom Ogilvy" wrote in message
...
Tilde is a special character, so you would have to double up the tilde

?
application.Match("Micro~~100",Array("Micro~10","M icro~50","Micro~100","Micr
o~400"),0)
3

as an example,

--

Regards,
Tom Ogilvy

"Scott" wrote in message
...
Thanks Tom,

I think the Problem is the tilde (~) in the sheet name, it doesn't seem to
recognise it.
the sheet name is something like this, Mirco~100.
Is there any way this can be fixed as the sheet name cannot be renamed.

Hope you can help,
Thanks
Best regards
Scott

"Tom Ogilvy" wrote in message
...
There is no change required - it isn't case sensitive as written.

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






"Tom Ogilvy" wrote in message
...
res = application.Match(activesheet.name,Array("sheet1", "sheet2"),0)
if not iserror(res) then
exit sub
End if

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active
sheet
from a list in an array, or something like that, if this sheet is in
the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott









--

Dave Peterson