Subscript out of range error?
in [Programming]
|
Prev: David
Next: Select a range within a range
From: gumby on 26 Jul 2006 19:42 I keep getting a subscript out of range error with this macro. Any help would be great. Public Sub AddSheetToEnd() 'Create a new sheet Dim NewWorksheet As Worksheet Set NewWorksheet = _ Application.Sheets.Add( _ After:=Worksheets(GetLastSheet), _ Type:=XlSheetType.xlWorksheet) ' Rename the worksheet NewWorksheet.Name = "Added Worksheet" ' Place a title in the worksheet. NewWorksheet.Cells(1, 1) = "Sample Data" ' Add some headings. NewWorksheet.Cells(3, 1) = "Lable" NewWorksheet.Cells(3, 2) = "Data" NewWorksheet.Cells(3, 3) = "Sum" ' Format the title and headings. With NewWorksheet.Range("A1", "B1") .Font.Bold = True .Font.Size = 12 .Borders.LineStyle = XlLineStyle.xlContinuous .Borders.Weight = XlBorderWeight.xlThick .Interior.Pattern = XlPattern.xlPatternAutomatic .Interior.Color = RGB(255, 255, 0) End With NewWorksheet.Range("A3", "C3").Font.Bold = True ' Create some data entries. Dim Counter As Integer For Counter = 1 To 6 ' Add some data labels. NewWorksheet.Cells(Counter + 3, 1) = _ "Element " + CStr(Counter) ' Add Random integer value between 1 and 10. NewWorksheet.Cells(Counter + 3, 2) = _ CInt(Rnd() * 10) ' Add an equation to the third column If Counter = 1 Then NewWorksheet.Cells(Count + 3, 3) = _ "=B" + CStr(Counter + 3) Else NewWorksheet.Cells(Counter + 3, 3) = _ "= C" + CStr(Counter + 2) + _ " + B" + CStr(Counter + 3) End If Next End Sub
From: moon on 26 Jul 2006 20:25 Set NewWorksheet = Sheets.Add(After:=Worksheets(Sheets.Count), Count:=1, Type:=xlWorksheet) "gumby" <david.isaacks(a)mail.va.gov> schreef in bericht news:1153957330.148572.218980(a)i3g2000cwc.googlegroups.com... >I keep getting a subscript out of range error with this macro. Any > help would be great. > > Public Sub AddSheetToEnd() > 'Create a new sheet > Dim NewWorksheet As Worksheet > > > Set NewWorksheet = _ > Application.Sheets.Add( _ > After:=Worksheets(GetLastSheet), _ > Type:=XlSheetType.xlWorksheet) > > > ' Rename the worksheet > NewWorksheet.Name = "Added Worksheet" > > > ' Place a title in the worksheet. > NewWorksheet.Cells(1, 1) = "Sample Data" > > > ' Add some headings. > NewWorksheet.Cells(3, 1) = "Lable" > NewWorksheet.Cells(3, 2) = "Data" > NewWorksheet.Cells(3, 3) = "Sum" > > > ' Format the title and headings. > With NewWorksheet.Range("A1", "B1") > .Font.Bold = True > .Font.Size = 12 > .Borders.LineStyle = XlLineStyle.xlContinuous > .Borders.Weight = XlBorderWeight.xlThick > .Interior.Pattern = XlPattern.xlPatternAutomatic > .Interior.Color = RGB(255, 255, 0) > End With > NewWorksheet.Range("A3", "C3").Font.Bold = True > > > ' Create some data entries. > Dim Counter As Integer > For Counter = 1 To 6 > > > ' Add some data labels. > NewWorksheet.Cells(Counter + 3, 1) = _ > "Element " + CStr(Counter) > > > ' Add Random integer value between 1 and 10. > NewWorksheet.Cells(Counter + 3, 2) = _ > CInt(Rnd() * 10) > > > ' Add an equation to the third column > If Counter = 1 Then > NewWorksheet.Cells(Count + 3, 3) = _ > "=B" + CStr(Counter + 3) > Else > NewWorksheet.Cells(Counter + 3, 3) = _ > "= C" + CStr(Counter + 2) + _ > " + B" + CStr(Counter + 3) > End If > Next > > > End Sub >
From: Dave Peterson on 26 Jul 2006 20:28 You have an active thread in .excel, don't you? gumby wrote: > > I keep getting a subscript out of range error with this macro. Any > help would be great. > > Public Sub AddSheetToEnd() > 'Create a new sheet > Dim NewWorksheet As Worksheet > > Set NewWorksheet = _ > Application.Sheets.Add( _ > After:=Worksheets(GetLastSheet), _ > Type:=XlSheetType.xlWorksheet) > > ' Rename the worksheet > NewWorksheet.Name = "Added Worksheet" > > ' Place a title in the worksheet. > NewWorksheet.Cells(1, 1) = "Sample Data" > > ' Add some headings. > NewWorksheet.Cells(3, 1) = "Lable" > NewWorksheet.Cells(3, 2) = "Data" > NewWorksheet.Cells(3, 3) = "Sum" > > ' Format the title and headings. > With NewWorksheet.Range("A1", "B1") > .Font.Bold = True > .Font.Size = 12 > .Borders.LineStyle = XlLineStyle.xlContinuous > .Borders.Weight = XlBorderWeight.xlThick > .Interior.Pattern = XlPattern.xlPatternAutomatic > .Interior.Color = RGB(255, 255, 0) > End With > NewWorksheet.Range("A3", "C3").Font.Bold = True > > ' Create some data entries. > Dim Counter As Integer > For Counter = 1 To 6 > > ' Add some data labels. > NewWorksheet.Cells(Counter + 3, 1) = _ > "Element " + CStr(Counter) > > ' Add Random integer value between 1 and 10. > NewWorksheet.Cells(Counter + 3, 2) = _ > CInt(Rnd() * 10) > > ' Add an equation to the third column > If Counter = 1 Then > NewWorksheet.Cells(Count + 3, 3) = _ > "=B" + CStr(Counter + 3) > Else > NewWorksheet.Cells(Counter + 3, 3) = _ > "= C" + CStr(Counter + 2) + _ > " + B" + CStr(Counter + 3) > End If > Next > > End Sub -- Dave Peterson
From: gumby on 26 Jul 2006 20:37 Thank you, that worked - but I wonder why GetLastSheet was not working? moon wrote: > Set NewWorksheet = Sheets.Add(After:=Worksheets(Sheets.Count), Count:=1, > Type:=xlWorksheet) > > > > "gumby" <david.isaacks(a)mail.va.gov> schreef in bericht > news:1153957330.148572.218980(a)i3g2000cwc.googlegroups.com... > >I keep getting a subscript out of range error with this macro. Any > > help would be great. > > > > Public Sub AddSheetToEnd() > > 'Create a new sheet > > Dim NewWorksheet As Worksheet > > > > > > Set NewWorksheet = _ > > Application.Sheets.Add( _ > > After:=Worksheets(GetLastSheet), _ > > Type:=XlSheetType.xlWorksheet) > > > > > > ' Rename the worksheet > > NewWorksheet.Name = "Added Worksheet" > > > > > > ' Place a title in the worksheet. > > NewWorksheet.Cells(1, 1) = "Sample Data" > > > > > > ' Add some headings. > > NewWorksheet.Cells(3, 1) = "Lable" > > NewWorksheet.Cells(3, 2) = "Data" > > NewWorksheet.Cells(3, 3) = "Sum" > > > > > > ' Format the title and headings. > > With NewWorksheet.Range("A1", "B1") > > .Font.Bold = True > > .Font.Size = 12 > > .Borders.LineStyle = XlLineStyle.xlContinuous > > .Borders.Weight = XlBorderWeight.xlThick > > .Interior.Pattern = XlPattern.xlPatternAutomatic > > .Interior.Color = RGB(255, 255, 0) > > End With > > NewWorksheet.Range("A3", "C3").Font.Bold = True > > > > > > ' Create some data entries. > > Dim Counter As Integer > > For Counter = 1 To 6 > > > > > > ' Add some data labels. > > NewWorksheet.Cells(Counter + 3, 1) = _ > > "Element " + CStr(Counter) > > > > > > ' Add Random integer value between 1 and 10. > > NewWorksheet.Cells(Counter + 3, 2) = _ > > CInt(Rnd() * 10) > > > > > > ' Add an equation to the third column > > If Counter = 1 Then > > NewWorksheet.Cells(Count + 3, 3) = _ > > "=B" + CStr(Counter + 3) > > Else > > NewWorksheet.Cells(Counter + 3, 3) = _ > > "= C" + CStr(Counter + 2) + _ > > " + B" + CStr(Counter + 3) > > End If > > Next > > > > > > End Sub > >
From: gumby on 26 Jul 2006 20:38
Dave, I am not sure I understand what you mean by thread. I have a few sheets? David Dave Peterson wrote: > You have an active thread in .excel, don't you? > > gumby wrote: > > > > I keep getting a subscript out of range error with this macro. Any > > help would be great. > > > > Public Sub AddSheetToEnd() > > 'Create a new sheet > > Dim NewWorksheet As Worksheet > > > > Set NewWorksheet = _ > > Application.Sheets.Add( _ > > After:=Worksheets(GetLastSheet), _ > > Type:=XlSheetType.xlWorksheet) > > > > ' Rename the worksheet > > NewWorksheet.Name = "Added Worksheet" > > > > ' Place a title in the worksheet. > > NewWorksheet.Cells(1, 1) = "Sample Data" > > > > ' Add some headings. > > NewWorksheet.Cells(3, 1) = "Lable" > > NewWorksheet.Cells(3, 2) = "Data" > > NewWorksheet.Cells(3, 3) = "Sum" > > > > ' Format the title and headings. > > With NewWorksheet.Range("A1", "B1") > > .Font.Bold = True > > .Font.Size = 12 > > .Borders.LineStyle = XlLineStyle.xlContinuous > > .Borders.Weight = XlBorderWeight.xlThick > > .Interior.Pattern = XlPattern.xlPatternAutomatic > > .Interior.Color = RGB(255, 255, 0) > > End With > > NewWorksheet.Range("A3", "C3").Font.Bold = True > > > > ' Create some data entries. > > Dim Counter As Integer > > For Counter = 1 To 6 > > > > ' Add some data labels. > > NewWorksheet.Cells(Counter + 3, 1) = _ > > "Element " + CStr(Counter) > > > > ' Add Random integer value between 1 and 10. > > NewWorksheet.Cells(Counter + 3, 2) = _ > > CInt(Rnd() * 10) > > > > ' Add an equation to the third column > > If Counter = 1 Then > > NewWorksheet.Cells(Count + 3, 3) = _ > > "=B" + CStr(Counter + 3) > > Else > > NewWorksheet.Cells(Counter + 3, 3) = _ > > "= C" + CStr(Counter + 2) + _ > > " + B" + CStr(Counter + 3) > > End If > > Next > > > > End Sub > > -- > > Dave Peterson |