VBA With Excel 2000 Problem
VBA With Excel 2000 Problem
(OP)
I'm trying to write a VBA macro that will, when given a CSV format file, take the appropriate data and create a graph. The problem I'm running into is that when I run a particular section:
Sub CreateGraphE4()
Range(Range("E4"), ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers)).Select
ActiveWindow.ScrollColumn = 1
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("output_file").Range(Range("E4"), ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers))
ActiveChart.Location Where:=xlLocationAsObject, Name:="output_file"
End Sub
I get the error
"Run-time error 1004: Method 'Range' of object '_Global' failed"
The strange part is that the macro will generate the graph anyway (after I select END from the popup). Any ideas as to why I get this error?
Sub CreateGraphE4()
Range(Range("E4"), ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers)).Select
ActiveWindow.ScrollColumn = 1
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("output_file").Range(Range("E4"), ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers))
ActiveChart.Location Where:=xlLocationAsObject, Name:="output_file"
End Sub
I get the error
"Run-time error 1004: Method 'Range' of object '_Global' failed"
The strange part is that the macro will generate the graph anyway (after I select END from the popup). Any ideas as to why I get this error?





RE: VBA With Excel 2000 Problem
Range(Range("E4"), ActiveCell).SpecialCells(xlCellTypeConstants, xlNumbers).Select
in place of
Range(Range("E4"), ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers)).Select
Note the closing ellipsis shown in bold.
Hope this helps
RE: VBA With Excel 2000 Problem
Is this a problem with xlNumbers? Does it only recognize INTEGER formatted numbers?
RE: VBA With Excel 2000 Problem
As far as I can make out, it was a simple case of improper ellipsis-balancing.
RE: VBA With Excel 2000 Problem
RE: VBA With Excel 2000 Problem
Assumptions:
1. The worksheet is named data_file
2. The data LABELS start in cell B4 (are listed in a single column)
3. The data ENTRIES start in cell E4
4. The x-coordinate labels start in cell E3 (are listed in a single row)
5. The desired graph is a Line Graph
Of course, you can change any of these fields to suit your needs.
Sub Chart01()
Worksheets("data_file").Activate
'Selects the data to be included in the graph
Range(Range("E3"), ActiveSheet.Cells(LastRowInColumn, LastOneInColumn)).Select
'Creating the chart
Charts.Add
'Selecting chart type
ActiveChart.ChartType = xlLineMarkers
'Setting Title for Chart
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = "My Chart"
'Embeds chart in current worksheet
ActiveChart.Location Where:=xlLocationAsObject, Name:="data_file"
Dim j As Integer
'Sets lables for chart data
For j = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(j).Name = Range("B4").Cells(j, 1)
Next j
ActiveChart.Legend.Font.Size = 8
End Sub
Function LastRowInColumn()
'Find last cell by searching upwards
Range("A65536").End(xlUp).Select
LastRowInColumn = ActiveCell.Row
End Function
Function LastOneInColumn()
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by columns.
Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Select
LastOneInColumn = ActiveCell.Column
End If
End Function