| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checking numbers across various sheets | Excel Worksheet Functions | |||
| Checking for Multiple Values | Excel Discussion (Misc queries) | |||
| Checking names on correct line across sheets | Excel Worksheet Functions | |||
| Checking for a zero value in multiple columns | Excel Worksheet Functions | |||
| Checking a number of sheets | Excel Worksheet Functions | |||
Linear Mode
