Excel VBA for chart

Question

I have two sheets in a workbook. 1st sheet name is “summary” and another is “target”. I have chart in summary tab. I want to set the source data for that chart to target tab that contains date. Ex(11/01/2013 – 11/30/2013). Everyday I want to change the chart date for corresponding date. So I tried in excel vba as below:

sheets("Summary ").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.PlotArea.Select
Sheets("Target").Select
a = InputBox("enter the date - format(mm/dd/yyyy)")

Set findrow = Range("a:a").Find(what:=a, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Rows

findrownumber = findrow.Row

ActiveChart.SeriesCollection(2).Values = "='Target Chart'!R4C78:R" & findrownumber & "C78"
End sub

While I am trying to enter source data value in formula it shows error.

Please help me.

score 1 · Answer 1 · edited May 23 '17 at 12:28

This is the code that I tried and it works. I might have switched the names of the sheet while creating this example but you can change that in the code :)

Let's say the Summary Tab looks like this

enter image description here

And your Chart in Target Sheet looks like this at the moment. The source data is set to =Summary!$A$1:$A$6

enter image description here

Now Try this code

Option Explicit

Sub Sample()
    Dim wsSum As Worksheet, wsTgt As Worksheet
    Dim objChart As ChartObject, chrt As Chart
    Dim sDate
    Dim findrow As Long
    Dim aCell As Range

    '~~> Accept the date
    sDate = InputBox("enter the date - format(mm/dd/yyyy)")

    '~~> Check if user entered something
    If sDate <> "" Then
        '~~> Set your respective worksheets
        Set wsSum = ThisWorkbook.Sheets("Summary")
        Set wsTgt = ThisWorkbook.Sheets("Target")

        '~~> Find the date in the cell
        With wsSum
            For Each aCell In .Columns(1).Cells
                If Format(aCell.Value, "mm/dd/yyyy") = sDate Then
                    '~~> Get the row number
                    findrow = aCell.Row
                    Exit For
                End If
            Next aCell
        End With

        '~~> Update the chart
        With wsTgt
            Set objChart = .ChartObjects("Chart 1")
            Set chrt = objChart.Chart

            chrt.SeriesCollection(1).Values = "='Summary'!R4C1:R" & findrow & "C1"
        End With
    End If
End Sub

When you run the code, in the Inputbox, set the date as "01/11/2013"

enter image description here

And this is the output which you will get with source data set as =Summary!$A$4:$A$11

enter image description here

IMPORTANT: I wouldn't recommend using Inputbox for capturing a date. You might want to use THIS

Excel VBA for chart

1 Answers1