none
HISTORY GRAPH USING EXCEL MACROS RRS feed

  • Question

  • Please help me with the following,

    In excel I have                

                 1st column   -  Date
                 2nd column -  Time
                 3rd column  -  X data( Numbers)

                 4th column  -  Y data (Numbers)

    What I need to do is that I should have two Input boxes, one to take the Start date and another one for End date.

    When I enter the start and end date the program should look for the data in those dates. And it should copy that data paste it in a new sheet then display a Line Graph.

    Will it be possible to do that with Excel macros? Kindly help me with this.

    Tuesday, May 19, 2015 4:43 AM

Answers

  • Hi,

    >> What I need to do is that I should have two Input boxes, one to take the Start date and another one for End date.

    You may use data validation to create two list to input Start date and End date by Validation.Add Method.The following code you could refer to, it has default set Cell(“G2”) for Start Date and Cell(“H2”) for End date.

    Sub CreateValidation()
        
    Range("G2:H2").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$A$2:$A$8"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End Sub

    >> When I enter the start and end date the program should look for the data in those dates. And it should copy that data paste it in a new sheet then display a Line Graph.

    You could use a loop for the date, and get the date in between the start date and end date. Then use Shapes.AddChart2 Method () to add for line chart on the sheet.

    Sub GetData()
    Dim Sh As Worksheet: Set Sh = Sheets("Sheet1")
    Dim i, j As Integer
    
    LookupColumn = "A" ' Default column A for Date
    StartDate_Value = Sh.Range("G2").Value 'Default G2 for StartDate
    EndDate_Value = Sh.Range("H2").Value   'Default H2 for EndDate
    
    For i = 1 To 30000
        If Sh.Range(LookupColumn & i).Value = EndDate_Value Then EndDate_Row = i
    Next i
    
    For j = EndDate_Row To 1 Step -1
        If Sh.Range(LookupColumn & j).Value = StartDate_Value Then StartDate_Row = j
    Next j
    
    Worksheets("Sheet2").Range(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row).Value = Sh.Range(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row).Value
    
    Call CreateChart(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row)
    End Sub.
    Sub CreateChart(RangeScope As String)
    '
        Range(RangeScope).Select
        ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range(RangeScope)
    End Sub

    Hope this could help you.

    Best Regards,

    Lan



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, May 20, 2015 4:32 AM
    Moderator

All replies

  • Maybe start here.

    https://www.dropbox.com/s/9i64ltwaovk5rwy/Filter%2C%20Between%20Date%20Range.xls?dl=0

    Then, just record a Macro to build the graph. 


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, May 19, 2015 4:11 PM
  • Hi,

    >> What I need to do is that I should have two Input boxes, one to take the Start date and another one for End date.

    You may use data validation to create two list to input Start date and End date by Validation.Add Method.The following code you could refer to, it has default set Cell(“G2”) for Start Date and Cell(“H2”) for End date.

    Sub CreateValidation()
        
    Range("G2:H2").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$A$2:$A$8"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End Sub

    >> When I enter the start and end date the program should look for the data in those dates. And it should copy that data paste it in a new sheet then display a Line Graph.

    You could use a loop for the date, and get the date in between the start date and end date. Then use Shapes.AddChart2 Method () to add for line chart on the sheet.

    Sub GetData()
    Dim Sh As Worksheet: Set Sh = Sheets("Sheet1")
    Dim i, j As Integer
    
    LookupColumn = "A" ' Default column A for Date
    StartDate_Value = Sh.Range("G2").Value 'Default G2 for StartDate
    EndDate_Value = Sh.Range("H2").Value   'Default H2 for EndDate
    
    For i = 1 To 30000
        If Sh.Range(LookupColumn & i).Value = EndDate_Value Then EndDate_Row = i
    Next i
    
    For j = EndDate_Row To 1 Step -1
        If Sh.Range(LookupColumn & j).Value = StartDate_Value Then StartDate_Row = j
    Next j
    
    Worksheets("Sheet2").Range(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row).Value = Sh.Range(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row).Value
    
    Call CreateChart(LookupColumn & StartDate_Row & ":" & "D" & EndDate_Row)
    End Sub.
    Sub CreateChart(RangeScope As String)
    '
        Range(RangeScope).Select
        ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range(RangeScope)
    End Sub

    Hope this could help you.

    Best Regards,

    Lan



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, May 20, 2015 4:32 AM
    Moderator
  • Why don't you plot all the data in the sheet, then filter the data range based on the date range. The hidden rows will not appear in the chart.


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Friday, May 22, 2015 2:09 PM