Wrox Programmer Forums
|
BOOK: Excel 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2003 VBA Programmer's Reference by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg, Brian Patterson; ISBN: 9780764556609
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 13th, 2009, 12:44 PM
Registered User
 
Join Date: Jul 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default runtime error 424: object required

Hi

The code is a Macro, that draws out a chart objects from data in columns B to whatever. Column B has the X axis data and Y axis values are on other columns. Basically it's multiple series line graph. The first series goes well, sometimes couple of series goes well and then it throws runtime error 424: object required. While I do stepin debug, I notice the variable C1 in watch window goes "Expression not defined..." it was defined before the while loop and then it throws the error.

Code:
Sub CleanUp()
Application.ScreenUpdating = False
Dim wrkBook As String
wrkBook = ActiveSheet.name
Dim MaxValRange, MaxVal
 
Set MaxValRange = Range("C:C")
MaxVal = Application.WorksheetFunction.Max(MaxValRange)
With MaxValRange
Dim C1, BegAdd
Dim BegCell
Dim BegValue, nextValue
Dim XBeg
Dim colCount As Integer
'Dim colCount
Dim CAve As Double
Dim CStdDev As Double
 
Dim BegCellY() As String
Dim EndCell() As String
Dim ValuesString() As String
'Count the number of columns in the worksheet
colCount = (Worksheets(wrkBook).UsedRange.Columns.count) - 2
ReDim BegCellY(1 To colCount) As String
ReDim EndCell(1 To colCount) As String
ReDim ValuesString(1 To colCount) As String
'C and BegAdd has the third column
Set C1 = .Find(MaxVal, LookIn:=xlValues)
BegAdd = C1.Address
'Finding BegCell
Range(C1.Address).Select
Range("A" & ActiveCell.Row).Select
'ActiveCell now is in first column
BegValue = ActiveCell.Value
'Moving up, finding the BegCell for the same date as in first column
Do
ActiveCell.Offset(-1, 0).Select
nextValue = ActiveCell.Value
'Deleting Empty Rows Can be copied to another sheet ////\\\??
For i = 1 To colCount
    If (ActiveCell.Offset(0, 1 + i).Value = 0 Or ActiveCell.Offset(0, 1 + i).Value = "?" Or ActiveCell.Offset(0, 1 + i).Value = "<>") Then
        ActiveCell.Rows.EntireRow.Delete
    End If
Next i
Loop While (nextValue = BegValue)
'BegCell has the first value in second Column
ActiveCell.Offset(1, 1).Select
BegCell = ActiveCell.Address
BegCellY(1) = ActiveCell.Offset(0, 1).Address '////\\\
'Finding EndCell
Range(C1.Address).Select '' This is where the problem arise.
Range("A" & ActiveCell.Row).Select
'ActiveCell now is in first column
BegValue = ActiveCell.Value
'Moving down, finding the EndCell for the same date as in first column
Do
ActiveCell.Offset(1, 0).Select
nextValue = ActiveCell.Value
'Deleting Empty Rows Can be copied to another sheet ////\\\
For i = 1 To colCount
    If (ActiveCell.Offset(0, 1 + i).Value = 0 Or ActiveCell.Offset(0, 1 + i).Value = "?" Or ActiveCell.Offset(0, 1 + i).Value = "<>") Then
        ActiveCell.Rows.EntireRow.Delete
    End If
Next i
Loop While (nextValue = BegValue)
ActiveCell.Offset(-1, 1).Select
EndCell(1) = ActiveCell.Offset(0, 1).Address '////\\\\
'Storing end value of X values
XBeg = ActiveCell.Address
For i = 1 To (colCount - 1)
    Range(BegCellY(i)).Offset(0, 1).Select
    BegCellY(i + 1) = ActiveCell.Address
 
    Range(EndCell(i)).Offset(0, 1).Select
    EndCell(i + 1) = ActiveCell.Address
Next i
Dim XValuesString As String
XValuesString = BegCell & ":" & XBeg
 
    Sheets.Add After:=Sheets(Sheets.count)
    ActiveSheet.Shapes.AddChart.Select
    ActiveSheet.name = wrkBook & " Chart"
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Sheets(wrkBook).Range(XValuesString)
 
 
For i = 1 To (colCount)
    ActiveChart.SeriesCollection.NewSeries
    ValuesString(i) = BegCellY(i) & ":" & EndCell(i)
    ActiveChart.SeriesCollection(i).name = i
    ActiveChart.SeriesCollection(i).Values = Sheets(wrkBook).Range(ValuesString(i))
Next i
 
    'Resize the chart
    With ActiveChart.Parent
        .Height = 500 ' resize
        .Width = 750 ' resize
        .Top = 100 ' reposition
        .Left = 100 ' reposition
    End With
    'Setting X and Y axis
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Temperature Vs Time"
        .Legend.Position = xlLegendPositionBottom
 
 
        'X axis
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (Hrs)"
        .Axes(xlCategory).TickLabels.NumberFormat = "h;@"
        .Axes(xlCategory).TickLabelSpacing = "60"
 
 
        'Y axis
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature (deg F)"
        .Axes(xlValue).TickLabels.NumberFormat = "0.0"
 
    End With
End With
Application.ScreenUpdating = True
End Sub
 
' I call above Sub from the code below
 
Sub CallSheets()
Dim aSheet As Integer
Dim count
Dim name As String
count = ActiveWorkbook.Sheets.count
Sheets(1).Select
For aSheet = 1 To count
    Call CleanUp
 
    name = Sheets(aSheet + 1).name
    Sheets(name).Select
Next aSheet
    'Call MaxValues
End Sub
First I thought it was memory problem. But that knowhow is no good, since the data sheets are huge (in the range of 8 to 20 MB).

I appreciate any light you can throw into this.

Thanks,
Anjana.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Time Error 424 "Object Required" paul31berks Access VBA 8 October 31st, 2010 04:49 PM
VBA Object required - RTE 424 dbrother Access VBA 2 November 2nd, 2007 04:06 PM
VBScript runtime (0x800A01A8) Object required SeanW Classic ASP Databases 8 February 21st, 2007 01:03 AM
Runtime Error 424: Object needed tobian Excel VBA 4 February 20th, 2006 10:41 AM
runtime error '800a01a8' Object Required: 'Session footohi Classic ASP Basics 1 July 17th, 2003 06:32 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.