CODEWITHSUNDEEP
vbaexcel
user1774673
user1774673

Reputation: 53

Excel 2010 VBA - Update Graph using range defined by variable

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.

Upvotes: 3

Views: 25932

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

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

Upvotes: 3

Related Questions