CODEWITHSUNDEEP
excelvbagraph
ztee
ztee

Reputation: 3

VBA code for plotting graph with given starting point and ending point

I would like to ask how to create a VBA code to plot a clustered column chart with the given starting point and ending point? I use inputbox function to allow the user to state the starting point. For example, the starting point is Jan-20. Then, the code needs to figure out the cell name of the starting point from a set of data, for example, A30. Then the user needs to key in the ending point in order to define the range of data that need to be plotted, for example, Feb-21, which is at the cell A45. Then the data range for the graph should be from A30:A45, B30:B45.

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

Thanks in advance

Upvotes: 0

Views: 474

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next code:

Sub createChartChooseSt_EndDate()
   Dim cht As Shape, sh As Worksheet, LastRA As Long, rngPlot As Range
   Dim startP, endP, cellStart As Range, cellEnd As Range
   
   Set sh = ActiveSheet 'use here the sheet you need
   
   LastRA = sh.Range("A" & sh.rows.Count).End(xlUp).row
   'use the standard date format according to your regional settings:
   startP = InputBox("Please enter the Starting poind date (Format dd/mm/yyyy)", "Choose starting date", Date)
   If Not IsDate(startP) Then MsgBox "No valid date format entered (start)...": Exit Sub
   
   endP = InputBox("Please enter the Ending poind date (Format dd/mm/yyyy)", "Choose ending date", Date + 10)
   If Not IsDate(endP) Then MsgBox "No valid date format entered (end)...": Exit Sub

   'create the range to be plotted:
   'find cellStart:
   Set cellStart = sh.Range("A1:A" & LastRA).Find(what:=CDate(startP), lookAt:=xlWhole, LookIn:=xlValues, After:=sh.Range("A1"), SearchDirection:=xlNext)
   If cellStart Is Nothing Then MsgBox "No start date found...": Exit Sub

   'find cellEnd:
   Set cellEnd = sh.Range("A1:A" & LastRA).Find(what:=CDate(endP), lookAt:=xlWhole, LookIn:=xlValues, After:=cellStart, SearchDirection:=xlNext)
   If cellEnd Is Nothing Then MsgBox "No end date found...": Exit Sub
   
   Set rngPlot = sh.Range(sh.cells(cellStart.row, 1), sh.cells(cellEnd.row, 2))
   Set cht = sh.Shapes.AddChart
    With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "My custom chart"
        .SetSourceData Source:=rngPlot
        .ChartType = xl3DBarClustered
    End With
End Sub

Please, send some feedback after testing it.

You can give a name to the created chart and next time you run the code, with some modifications, it may check if the chart exists and only change the data source for the newly chosen date start/ date end...

Upvotes: 1

Related Questions