Excel 2010 VBA - Update Graph using range defined by variable

Question

I have an Excel sheet that is updating daily. I am trying to automatically update a graph with the new data (1 row) that is added daily.

So far I have:

Sub UpdateGraphs()

    Dim latestRow As Integer

    Sheets("DailyJourneyProcessing").Select

    Range("A500").Select

    Do Until ActiveCell.Value = ""
        If ActiveCell.Value <> "" Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop

    ActiveCell.Offset(-1, 0).Select
    Application.CutCopyMode = False
    ActiveCell.EntireRow.Copy

    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.PasteSpecial (xlPasteAll)

    Application.CutCopyMode = False

    latestRow = ActiveCell.row

    Dim str1 As String
    Dim rng1 As Range

    str1 = "=DailyJourneyProcessing!$F$180:$F$" & latestRow
    Set rng1 = Range(str1)

    Debug.Print "Got this far..."

    Set ActiveChart.SeriesCollection(1).Values = Range(str1)

I know that this looks like I simply copy the previous row but the formula's included take car of the changes in data.

The Integer / row at the moment is around 520, so I want to do:

ActiveChart.SeriesCollection(1).Values = "=DailyJourneyProcessing!$F$180:$F$520"

Where the row number changes daily. This is one of about 20 range updates I need to automate, but once I have solved one the others should be the same.

I have tried everything I can find online, but nothing quite worked.

At the moment, I get a run-time error 91: Object or With block variable not set.

Any help would be appreciated.

Scott Holtzman · Accepted Answer · 2012-10-25 16:11:05Z

3

There is actually no need for VBA to accomplish this. You will find the method in this link much easier to manage and maintain than VBA code. Also, its really best not to use VBA when you don't have to!

However, so that you can see a more efficient way to code what you were trying to do, I've offered the code below. It very well may need some tweaks to fit your actual data set.

Sub UpdateGraphs()

    Dim wks As Worksheet, rng1 As Range
    Dim latestRow As Long ' changed to long to handle rows over 32,000 (whatever number Integer stops at)

    Set wks = Sheets("DailyJourneyProcessing")

    With wks

        latestRow = .Range("F" & .Rows.Count).End(xlUp).Row

        str1 = "=DailyJourneyProcessing!$F$180:$F$" & latestRow

        Set rng1 = Range(str1)

        Dim myChart As Chart
        Set myChart = .ChartObjects("myChartName")

        myChart.SeriesCollection(1).Values = rng1

    End With

End Sub

edited Oct 25, 2012 at 16:11

answered Oct 25, 2012 at 15:49

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel 2010 VBA - Update Graph using range defined by variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related