VBA Insert value into range

[jamescooper]

Days should equal 34, and trying to reference it in the range - any ideas here please? it is failing on this line:

ActiveChart.FullSeriesCollection(1).Values = "=Revenue!O6:O & Days"

Sub Chart()


    Dim RngToCover As Range
    Dim ChtOb As ChartObject
    Set RngToCover = ActiveSheet.Range("E5:N20")
    
    Dim myvalue3 As Single
    Dim Lookup_Range As Range
    Dim Days As Single
    
    myvalue3 = 1802
    
    Set Lookup_Range = Sheets("Periods").Range("G2:H100")
    
    Days = (Application.WorksheetFunction.VLookup(myvalue3, Lookup_Range, 2, False)) + 6
 
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Values = "=Revenue!O6:O & Days"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Values = "=EBM!O6:O & Days"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(3).Values = "=TMC!O6:O & Days"
    
    Set ChtOb = ActiveChart.Parent
         
         ChtOb.Height = RngToCover.Height ' resize
         ChtOb.Width = RngToCover.Width   ' resize
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
         
End Sub

Many thanks.

 

[pgc01]

Hi

I guess you mean

... = "=Revenue!O6:O" & Days