Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2006

Tip: Looking for answers? Try searching our database.

Subscript out of range error?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
gumby - 26 Jul 2006 22:09 GMT
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 = _
   Applicaiton.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 - 26 Jul 2006 22:19 GMT
This isn't the problem, but I'd change:  Applicaiton  to Application

If the error occurs on this line:

Set NewWorksheet = _
   Applicaiton.Sheets.Add( _
       After:=Worksheets(GetLastSheet), _
       Type:=XlSheetType.xlWorksheet)

You'll want to share what GetLastSheet is.

> I keep getting a subscript out of range error with this macro.  Any
> help would be great.
[quoted text clipped - 54 lines]
>
> End Sub

Signature

Dave Peterson


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.