Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get Subscript out of Range second time, not the first time
Hello:
I am using a marco to copy two sheet into a workbook from an existing sheet in same workbook. I want the user to have the ability to run the marco more than once which updates the sheet by going to the database, there fore I don't want the sheets copied more than once.. I check to see if sheet exist, if not I copy it. It works with the fisrst sheet, but when I do the test on the second sheet I get subscript out of range. When I run the macro a second time it works. Its not consistant What am I doing wrong the logic is the same? SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets("CCB_Host_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet already Exist") End If NoSuchSheet: If SheetExists = False Then Sheets("Sheet1").Copy Befo=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "LogicalSheet" Range("C13").Select MsgBox ("Adding Sheet") End If SheetExists = False On Error GoTo Sheet If Len(Sheets("3G_Triage_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet2 already Exist") End If Sheet: If Sheet2Exists = False Then Sheets("Sheet1").Copy Befo=Sheets(2) 'Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "Testsheet" Range("C13").Select MsgBox ("No sheet2 exist Adding sheet") End If Thank You Pam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get Subscript out of Range second time, not the first time
On Feb 20, 1:04 am, "Pam" wrote:
Hello: I am using a marco to copy two sheet into a workbook from an existing sheet in same workbook. I want the user to have the ability to run the marco more than once which updates the sheet by going to the database, there fore I don't want the sheets copied more than once.. I check to see if sheet exist, if not I copy it. It works with the fisrst sheet, but when I do the test on the second sheet I get subscript out of range. When I run the macro a second time it works. Its not consistant What am I doing wrong the logic is the same? SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets("CCB_Host_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet already Exist") End If NoSuchSheet: If SheetExists = False Then Sheets("Sheet1").Copy Befo=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "LogicalSheet" Range("C13").Select MsgBox ("Adding Sheet") End If SheetExists = False On Error GoTo Sheet If Len(Sheets("3G_Triage_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet2 already Exist") End If Sheet: If Sheet2Exists = False Then Sheets("Sheet1").Copy Befo=Sheets(2) 'Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "Testsheet" Range("C13").Select MsgBox ("No sheet2 exist Adding sheet") End If Thank You Pam Correction, the first logic works for the first sheet but dosen't work for the second sheet, I get subscript out of range. Then when I run the macro a second time it works. I can't delivery macro this way. HELP PLEASE!!!!! Thank You Pam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get Subscript out of Range second time, not the first time
Your problem is probably that your code address whatever workbook is active.
You need to qualify your code so it checks sheets in a particular workbook if you want consistent behavior. If no other way, you could loop through the workbooks collection and identify the workbook by some unique feature perhaps. -- Regards, Tom Ogilvy "Pam" wrote: On Feb 20, 1:04 am, "Pam" wrote: Hello: I am using a marco to copy two sheet into a workbook from an existing sheet in same workbook. I want the user to have the ability to run the marco more than once which updates the sheet by going to the database, there fore I don't want the sheets copied more than once.. I check to see if sheet exist, if not I copy it. It works with the fisrst sheet, but when I do the test on the second sheet I get subscript out of range. When I run the macro a second time it works. Its not consistant What am I doing wrong the logic is the same? SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets("CCB_Host_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet already Exist") End If NoSuchSheet: If SheetExists = False Then Sheets("Sheet1").Copy Befo=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "LogicalSheet" Range("C13").Select MsgBox ("Adding Sheet") End If SheetExists = False On Error GoTo Sheet If Len(Sheets("3G_Triage_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet2 already Exist") End If Sheet: If Sheet2Exists = False Then Sheets("Sheet1").Copy Befo=Sheets(2) 'Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "Testsheet" Range("C13").Select MsgBox ("No sheet2 exist Adding sheet") End If Thank You Pam Correction, the first logic works for the first sheet but dosen't work for the second sheet, I get subscript out of range. Then when I run the macro a second time it works. I can't delivery macro this way. HELP PLEASE!!!!! Thank You Pam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get Subscript out of Range second time, not the first time
On Feb 20, 7:32 am, Tom Ogilvy
wrote: Your problem is probably that your code address whatever workbook is active. You need to qualify your code so it checks sheets in a particular workbook if you want consistent behavior. If no other way, you could loop through the workbooks collection and identify the workbook by some unique feature perhaps. -- Regards, Tom Ogilvy "Pam" wrote: On Feb 20, 1:04 am, "Pam" wrote: Hello: I am using a marco to copy two sheet into a workbook from an existing sheet in same workbook. I want the user to have the ability to run the marco more than once which updates the sheet by going to the database, there fore I don't want the sheets copied more than once.. I check to see if sheet exist, if not I copy it. It works with the fisrst sheet, but when I do the test on the second sheet I get subscript out of range. When I run the macro a second time it works. Its not consistant What am I doing wrong the logic is the same? SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets("CCB_Host_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet already Exist") End If NoSuchSheet: If SheetExists = False Then Sheets("Sheet1").Copy Befo=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "LogicalSheet" Range("C13").Select MsgBox ("Adding Sheet") End If SheetExists = False On Error GoTo Sheet If Len(Sheets("3G_Triage_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet2 already Exist") End If Sheet: If Sheet2Exists = False Then Sheets("Sheet1").Copy Befo=Sheets(2) 'Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "Testsheet" Range("C13").Select MsgBox ("No sheet2 exist Adding sheet") End If Thank You Pam Correction, the first logic works for the first sheet but dosen't work for the second sheet, I get subscript out of range. Then when I run the macro a second time it works. I can't delivery macro this way. HELP PLEASE!!!!! Thank You Pam- Hide quoted text - - Show quoted text - Hi: What I did and it worked, I just split the logic up into to different fucntions and it's working fine now. I check if the first sheet exist in the original funcition and I moved the checking for second sheet into a different function. Maybe it didn't like the On Error Go to in the logic twice. Thank You for your suggestion. Pam. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get Subscript out of Range second time, not the first time
Upon closer examination of your code, Indirectly that is correct. It
doesn't like it because you never end the error handling and when you get an error in the error handler, it quits. Separating into separate functions does cause the error handling to end when the function is exited. but don't think you couldn't have kept them together with better organization and correct error handling. -- Regards, Tom Ogilvy "Pam" wrote in message oups.com... On Feb 20, 7:32 am, Tom Ogilvy wrote: Your problem is probably that your code address whatever workbook is active. You need to qualify your code so it checks sheets in a particular workbook if you want consistent behavior. If no other way, you could loop through the workbooks collection and identify the workbook by some unique feature perhaps. -- Regards, Tom Ogilvy "Pam" wrote: On Feb 20, 1:04 am, "Pam" wrote: Hello: I am using a marco to copy two sheet into a workbook from an existing sheet in same workbook. I want the user to have the ability to run the marco more than once which updates the sheet by going to the database, there fore I don't want the sheets copied more than once.. I check to see if sheet exist, if not I copy it. It works with the fisrst sheet, but when I do the test on the second sheet I get subscript out of range. When I run the macro a second time it works. Its not consistant What am I doing wrong the logic is the same? SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets("CCB_Host_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet already Exist") End If NoSuchSheet: If SheetExists = False Then Sheets("Sheet1").Copy Befo=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "LogicalSheet" Range("C13").Select MsgBox ("Adding Sheet") End If SheetExists = False On Error GoTo Sheet If Len(Sheets("3G_Triage_Analysis").Name) 0 Then SheetExists = True MsgBox ("Sheet2 already Exist") End If Sheet: If Sheet2Exists = False Then Sheets("Sheet1").Copy Befo=Sheets(2) 'Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "Testsheet" Range("C13").Select MsgBox ("No sheet2 exist Adding sheet") End If Thank You Pam Correction, the first logic works for the first sheet but dosen't work for the second sheet, I get subscript out of range. Then when I run the macro a second time it works. I can't delivery macro this way. HELP PLEASE!!!!! Thank You Pam- Hide quoted text - - Show quoted text - Hi: What I did and it worked, I just split the logic up into to different fucntions and it's working fine now. I check if the first sheet exist in the original funcition and I moved the checking for second sheet into a different function. Maybe it didn't like the On Error Go to in the logic twice. Thank You for your suggestion. Pam. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run-time error '9': subscript out of range | Excel Discussion (Misc queries) | |||
Run-time Error 9: Subscript out of range | Excel Programming | |||
run-time error '9': Subscript out of range - WHY?? | Excel Programming | |||
Run-time error 9 (Subscript out of range) | Excel Programming | |||
run-time error '9': Subscript out of range | Excel Programming |