Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
Hi all, I have macro below in which i am getting error on line "Set
Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
hi
with this line..... Set Wks = Worksheets(myCell.Value) excel is looking for the name of a worksheet not the value of mycell. try Set Wks = myCell.Value now the variable Wks has been set to the value of mycell. regards FSt1 "K" wrote: Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
Since that line is surrounded by On Error Resume Next and On Error GoTo 0,
I'd be surprised if that error didn't come up at all, unlees you remove those lines. It's half expected that the line will error, if it errors, it means the sheet tab name doesn't already exist, so one with that name can be added. If it doesn't error Set Wks sets Wks to something, if it does error, Wks becomes Nothing, which is tested for in the subsequent line. I think all is well. -- p45cal "K" wrote: Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
When you use the Set statement to create an object variable, the object must
already exist. The error occurs if the value in the cell does not equal an existing object, or if VBA cannot find the object where it would expect the object to be stored. In this case you are using a list of names for for worksheets and VBA is telling you that it cannot find one or more of those names as an existing worksheet. Check the spelling of the name in the list, make sure the name is for an existing worksheet and not a chartsheet. You can also manually execute the code using F8 to see what value myCell has at the time you attempt to Set the object variable. The key factor is that it is not finding a qualified object to Set. "K" wrote in message ... Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
It doesn't have to already exist:
dim NewWks as worksheet dim NewWkbk as workbook dim myCell as range Dim WordApp as Object dim WordApp2 as Word.Application set NewWks = worksheets.add set NewWkbk = workbooks.add set myCell = workbooks.add(1).worksheets(1).range("A1") Set WordApp = createobject("Word.Application") set WordApp2 = New Word.Application (but outside of .add or createobject or New or... I can't think of anything else...) JLGWhiz wrote: When you use the Set statement to create an object variable, the object must already exist. The error occurs if the value in the cell does not equal an existing object, or if VBA cannot find the object where it would expect the object to be stored. In this case you are using a list of names for for worksheets and VBA is telling you that it cannot find one or more of those names as an existing worksheet. Check the spelling of the name in the list, make sure the name is for an existing worksheet and not a chartsheet. You can also manually execute the code using F8 to see what value myCell has at the time you attempt to Set the object variable. The key factor is that it is not finding a qualified object to Set. "K" wrote in message ... Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
Guess I could have chosen my words better. <g
"Dave Peterson" wrote in message ... It doesn't have to already exist: dim NewWks as worksheet dim NewWkbk as workbook dim myCell as range Dim WordApp as Object dim WordApp2 as Word.Application set NewWks = worksheets.add set NewWkbk = workbooks.add set myCell = workbooks.add(1).worksheets(1).range("A1") Set WordApp = createobject("Word.Application") set WordApp2 = New Word.Application (but outside of .add or createobject or New or... I can't think of anything else...) JLGWhiz wrote: When you use the Set statement to create an object variable, the object must already exist. The error occurs if the value in the cell does not equal an existing object, or if VBA cannot find the object where it would expect the object to be stored. In this case you are using a list of names for for worksheets and VBA is telling you that it cannot find one or more of those names as an existing worksheet. Check the spelling of the name in the list, make sure the name is for an existing worksheet and not a chartsheet. You can also manually execute the code using F8 to see what value myCell has at the time you attempt to Set the object variable. The key factor is that it is not finding a qualified object to Set. "K" wrote in message ... Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 9
Just that one <vvbg.
JLGWhiz wrote: Guess I could have chosen my words better. <g "Dave Peterson" wrote in message ... It doesn't have to already exist: dim NewWks as worksheet dim NewWkbk as workbook dim myCell as range Dim WordApp as Object dim WordApp2 as Word.Application set NewWks = worksheets.add set NewWkbk = workbooks.add set myCell = workbooks.add(1).worksheets(1).range("A1") Set WordApp = createobject("Word.Application") set WordApp2 = New Word.Application (but outside of .add or createobject or New or... I can't think of anything else...) JLGWhiz wrote: When you use the Set statement to create an object variable, the object must already exist. The error occurs if the value in the cell does not equal an existing object, or if VBA cannot find the object where it would expect the object to be stored. In this case you are using a list of names for for worksheets and VBA is telling you that it cannot find one or more of those names as an existing worksheet. Check the spelling of the name in the list, make sure the name is for an existing worksheet and not a chartsheet. You can also manually execute the code using F8 to see what value myCell has at the time you attempt to Set the object variable. The key factor is that it is not finding a qualified object to Set. "K" wrote in message ... Hi all, I have macro below in which i am getting error on line "Set Wks = Worksheets(myCell.Value)" and in error message it says Run-time error '9': Subscript out of range Please can any friend guide me what i should be doing Private Sub CommandButton1_Click() 'CREATE TABS Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Wks As Worksheet Set TemplateWks = Worksheets("TEMPLATE") Set ListWks = Worksheets("SUMMARY") With ListWks Set ListRng = .Range("H7:H18") End With For Each myCell In ListRng.Cells Set Wks = Nothing On Error Resume Next Set Wks = Worksheets(myCell.Value) On Error GoTo 0 If Wks Is Nothing Then TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Err.Clear End If Else Beep MsgBox myCell.Value & " already exists" End If Next myCell End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |