Excel VBA Plotting

ameya

Board Regular
Joined
Jun 10, 2014
Messages
105
Hello everyone,

Could someone help me with the plotting of bar and line graphs given an arrays of x and y values in VBA? I want to plot the rDateRange and rOEERange. Thank you

Code:
Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
    
With Worksheets("Calculation")
    Set rInitialDate = .UsedRange.Find(What:=DateValue(InitialDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)
    Set rFinalDate = .UsedRange.Find(What:=DateValue(FinalDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchFormat:=False)
    If rInitialDate Is Nothing Or rFinalDate Is Nothing Then
        MsgBox "Not able to find both dates."
        Exit Sub
    Else
        Set rDateRange = .Range(rInitialDate, rFinalDate)
    End If
End With
Dim startRow As Integer, endRow As Integer, rDateRangeIndex As Integer, rOEERange As Range
startRow = rInitialDate.Row
endRow = rFinalDate.Row
With Worksheets("Calculation")
    Set startOEECell = .Cells(startRow, 45)
    Set endOEECell = .Cells(endRow, 45)
    Set rOEERange = .Range(startOEECell, endOEECell)
End With
'to create new Range to reflect MC,Shift,Date
With Worksheets("Calculation")
    For i = startRow To endRow Step 1
        If Cells(i, 7) Is Not MC.Value Or Cells(i, 6) Is Not Shift.Value _
            Or DateValue(rDateRange(i - (startRow - 1))) < DateValue(InitialDate) _
            Or DateValue(rDateRange(i - (startRow - 1))) > DateValue(FinalDate) Then
                rDateRange(i - (startRow - 1)).Delete
                rOEERange(i - (startRow - 1)).Delete
        End If
    Next i
End With
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
Wouldnt you just place the data in a tab called GRAPH, then plot the graph...
Then everytime you have new data, paste it into the GRAPH tab for an instant update? (with zero VBA)
 

ameya

Board Regular
Joined
Jun 10, 2014
Messages
105
The arrays are in VBA. The file is used by several people, all of whom do not know anything about how VBA works, so I must make it as user friendly as possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,731
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top