Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Subscript out of range error?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Subscript out of range error?
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 40
Default Subscript out of range error?

Set NewWorksheet = Sheets.Add(After:=Worksheets(Sheets.Count), Count:=1,
Type:=xlWorksheet)



"gumby" schreef in bericht
ups.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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Subscript out of range error?
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" schreef in bericht
ups.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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Subscript out of range error?
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Subscript out of range error?
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Subscript out of range error?
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Subscript out of range error?
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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Subscript out of range error?
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subscript out of range error - save copy error bg18461[_16_] Excel Programming 2 June 13th 06 04:53 PM
Subscript out of range error - save copy error bg18461[_15_] Excel Programming 1 June 13th 06 04:36 PM
Subscript out of range (Error 9) John in Surrey Excel Programming 1 January 11th 06 12:42 PM
Type Mismatch error & subscript out of range error Jeff Wright[_2_] Excel Programming 3 May 14th 05 07:14 PM
Subscript Out Of Range Error? Michael Vaughan Excel Programming 3 November 9th 04 11:35 AM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement