Subscript out of range error?
in [Programming]
|
Prev: David
Next: Select a range within a range
From: Dave Peterson on 26 Jul 2006 21:05 You posted the same question in .excel and had at least one response in that newsgroup. gumby wrote: > > 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 -- Dave Peterson
From: gumby on 28 Jul 2006 12:20 O,sorry I was not sure how to remove it after I posted in the wrong palce. David Dave Peterson wrote: > You posted the same question in .excel and had at least one response in that > newsgroup. > > > > gumby wrote: > > > > 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 > > -- > > Dave Peterson
From:
Dave Peterson on 28 Jul 2006 14:51
You can't remove it. But if you multipost like this, you'll have to check all the locations you posted--kind of a waste of time for you. And if you got your answer in one forum, then it's a waste of time for the other responders. gumby wrote: > > O,sorry I was not sure how to remove it after I posted in the wrong > palce. > > David > > Dave Peterson wrote: > > You posted the same question in .excel and had at least one response in that > > newsgroup. > > > > > > > > gumby wrote: > > > > > > 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 > > > > -- > > > > Dave Peterson -- Dave Peterson |