Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name
hi,
I have the following: For x = 1 to 30 dim previoussheet as string previoussheet = "acct" & x-1 set sh = worksheets(previoussheet) if not sheetexists(sh) then .... i think there is a problem with the following line: set sh = worksheets(previoussheet) because the standard syntax is usually something like: set sh = worksheets("previoussheet") i am not sure what to do. could someone help? thanks in advance, geebee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name
If I understand your question, one way:
Dim sh As Worksheet Dim x As Long For x = 1 To 30 On Error Resume Next Set sh = Worksheets("acct" & x - 1) On Error GoTo 0 If Not sh Is Nothing Then ' do stuff End If Set sh = Nothing Next x In article , geebee (noSPAMs) wrote: hi, I have the following: For x = 1 to 30 dim previoussheet as string previoussheet = "acct" & x-1 set sh = worksheets(previoussheet) if not sheetexists(sh) then ... i think there is a problem with the following line: set sh = worksheets(previoussheet) because the standard syntax is usually something like: set sh = worksheets("previoussheet") i am not sure what to do. could someone help? thanks in advance, geebee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name
i,
I am getting a "subscript out of range" error pointing to the Set sh = Worksheets("acct" & x - 1) line. geebee "JE McGimpsey" wrote: If I understand your question, one way: Dim sh As Worksheet Dim x As Long For x = 1 To 30 On Error Resume Next Set sh = Worksheets("acct" & x - 1) On Error GoTo 0 If Not sh Is Nothing Then ' do stuff End If Set sh = Nothing Next x In article , geebee (noSPAMs) wrote: hi, I have the following: For x = 1 to 30 dim previoussheet as string previoussheet = "acct" & x-1 set sh = worksheets(previoussheet) if not sheetexists(sh) then ... i think there is a problem with the following line: set sh = worksheets(previoussheet) because the standard syntax is usually something like: set sh = worksheets("previoussheet") i am not sure what to do. could someone help? thanks in advance, geebee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name
Do you have a sheet named acct0? If not, you will get the error message. I
wondered about that when I saw the x-1. Also make sure the Set statement is inside the For loop. "geebee" wrote: i, I am getting a "subscript out of range" error pointing to the Set sh = Worksheets("acct" & x - 1) line. geebee "JE McGimpsey" wrote: If I understand your question, one way: Dim sh As Worksheet Dim x As Long For x = 1 To 30 On Error Resume Next Set sh = Worksheets("acct" & x - 1) On Error GoTo 0 If Not sh Is Nothing Then ' do stuff End If Set sh = Nothing Next x In article , geebee (noSPAMs) wrote: hi, I have the following: For x = 1 to 30 dim previoussheet as string previoussheet = "acct" & x-1 set sh = worksheets(previoussheet) if not sheetexists(sh) then ... i think there is a problem with the following line: set sh = worksheets(previoussheet) because the standard syntax is usually something like: set sh = worksheets("previoussheet") i am not sure what to do. could someone help? thanks in advance, geebee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name
Since the code I gave you has that line right after
On Error Resume Next which tells VBA to ignore any errors, I'll assume that you've modified it somehow. Posting the code you actually used may help... In article , geebee (noSPAMs) wrote: i, I am getting a "subscript out of range" error pointing to the Set sh = Worksheets("acct" & x - 1) line. geebee "JE McGimpsey" wrote: If I understand your question, one way: Dim sh As Worksheet Dim x As Long For x = 1 To 30 On Error Resume Next Set sh = Worksheets("acct" & x - 1) On Error GoTo 0 If Not sh Is Nothing Then ' do stuff End If Set sh = Nothing Next x |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
Export sheet store sheet import sheet. | Excel Programming | |||
Copying cells from on sheet to another sheet (via sheet module) | Excel Programming | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |