Making chart with variable data start column

Question

I'm trying to make a chart with a dynamic range based on the FIND of specific text in a column from a range (data series by months). I need a chart of the data for the current and previous two months.

The idea is to find the column with the current month and use it and the previous two columns for chart data. However, I can't get the data for the prior two months.

Sub Chart()
    '
    ' Chart Macro
    '
    Dim ra As Range
    Dim SC As Range
    Dim EC As Range
      
    Set ra = Cells.Find(What:="March-2024", LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        
    Set SC = ra.Offset(0, -3)
    Set EC = ra.Offset(1, 0)
    
    If ra Is Nothing Then
        MsgBox ("Not found")
    Else
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Charts!$B$1:$B$2,SC:EC")
    End If
End Sub

The problem is the part with SC:EC which are not accepted as ranges.
It should find the start cell and data range for the chart to be set three columns back and one row below it.

Answer 1

To define multiple non-contiguous ranges for a Range object the string of the ranges must be comma separated and the 2 range objects Address property to use something like this:

Range("Charts!$B$1:$B$2," & "Charts!" & SC.address & ":" & EC.address)

Since it will be used for a Chart this result range must be formate a rectangle.