VBA With Excel 2000 Problem

[logius]

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?

 

[Mala]

Try:

Range(Range("E4&quot, ActiveCell).SpecialCells(xlCellTypeConstants, xlNumbers).Select

in place of

Range(Range("E4&quot, ActiveCell.SpecialCells(xlCellTypeConstants, xlNumbers)).Select

Note the closing ellipsis shown in bold.

Hope this helps

 

[logius]

Well, heck, that worked, but now I ran into a data recognition problem. This slection method was working great as long as my data was in INTEGER (non-decimal) format, but it doesn't want to pick up data when the values are in REAL (decimal) formats. I thought I had everything figured out, but when I was running through with test data this problem came up.

Is this a problem with xlNumbers? Does it only recognize INTEGER formatted numbers?

 

[Mala]

No, I don't know how it was working properly before...
As far as I can make out, it was a simple case of improper ellipsis-balancing.

 

[logius]

Well, can anyone figure a way to select a range of data where the number of rows and columns is variable? The only thing I ever know for sure is that the data starts in cell E4, but I don't know how many columns or rows there may be beyond that. The method I was using before was working about 90% of the time.

 

[logius]

Okay, here's what I did. In case anyone ever needs to create a chart when they don't know the row and column limits of their data are, here's a way of doing it.
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