Linking dynamically allocated row offset to stacked column chart data range variable

Hi,

Thank you for looking into this problem.

I have a workbook in which I have a summary sheet of all the data/calculations I need to refer-to/make-use-of in order to analyze some key pointers. I am using VBA to dynamically allocate a certain number of rows (ModuleStability) in that summary sheet and then populate those rows with data from other worksheets in my workbook with the help of another macro. I have created a stacked column chart to compare two things and the problem I was facing was that I needed to update that chart based on this new data that I populated the rows with. I wrote the below-mentioned macro to select/update the chart's data range. Now, the problem I'm facing with this is that while I can concretely define numbers for range extremes, I can't seem to figure out how to abstractly tell excel to set the source data for the chart to row:4 through (allocated variable) and column:C F G are used.

The actual code for the chart itself starts on line 13 (excluding blank lines), and everything before that (Line 1-12) is for summarizing/aggregating the rows that I populated with the previous macro I used. Clearly, I have had success with the summarizing even though there is a dynamically allocated variable (ModuleStability) involved with it's calculations but fail to do so with the chart. After reading through forums for a week and trying out different things, I have given up and finally posted on here. So any help would be really appreciated and am ready to get flamed for mistakes since this would be my first time posting.


Sub CalculatingStabilityRisk()
'
' CalculatingStabilityRisk Macro
'
ModuleStability = InputBox("Enter the number of modules including Module 1", "Number of modules", 1) 'Typically user would enter 5 or 6 and then it will drop down 5 rows, the implementation of which takes place in another macro
ModuleStability = ModuleStability + 4 'Since calculations will start from row 4
Range("D" & ModuleStability).Select
R1 = ActiveCell.Row
ActiveCell.Value = Application.Sum(Range(Cells(4, 4), Cells(R1 - 1, 4)))
ActiveCell.Value = Application.Sum(Range(Cells(4, 4), Cells(R1 - 1, 4)))
Range("E" & ModuleStability).Select
R2 = ActiveCell.Row
ActiveCell.Value = Application.Sum(Range(Cells(4, 5), Cells(R2 - 1, 5)))
ActiveCell.Value = Application.Sum(Range(Cells(4, 5), Cells(R2 - 1, 5)))

'(RowOffset has a -1 is since there exists one row by default)
RowOffset = ModuleStability - 1
Set ChartRange.Value = Sheets("Summary").Range(Cells(3, 3), Cells(RowOffset, 3))
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.SetSourceData Source:=ChartRange.Value
End Sub

'I also would typically want the chart range above to include Cells(3, 6) to (RowOffset, 6) for column F and Cells(3, 7) to (RowOffset, 7) for column G but whatever I have seems to be buggy.

Thank you once again!