×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA With Excel 2000 Problem

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?

RE: VBA With Excel 2000 Problem

Try:

    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

(OP)
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?

RE: VBA With Excel 2000 Problem

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.

RE: VBA With Excel 2000 Problem

(OP)
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.

RE: VBA With Excel 2000 Problem

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

eBook - Manufacturing the Cars of Tomorrow
In this ebook, we'll explore how additive manufacturing is going to transform the way cars are made. This includes commentary from thought leaders such as Ford's CTO, Ken Washington, Customer case studies of ways 3D printing is being used today, and a variety of part examples where 3D printing is already impacting how automobiles are made. Download Now
White Paper - Smart Manufacturing for Semiconductor
New technologies and approaches present great opportunities for semiconductor manufacturers to achieve high levels of innovation, yield and improvement. This white paper explores some of these cutting-edge technologies and how they can be applied effectively in the semiconductor industry. Read about how Smart Manufacturing is transforming the semiconductor industry. Download Now
White Paper - Analysis and Simulation in Aircraft Structure Certification
Organizations using simulation and analysis tools effectively see the benefits in their ability to achieve certification faster and with drastically less total cost than those who do not maximize these tools. Read this White Paper to learn about how digital tools such as analysis and simulation help in aircraft structure certification. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close