Based on a specified parameter given by the user, I need to create a formula array that will sum the total columns dictated by the users specified amount.
i.e. the user specifies the number of tasks, say x. Then my macro will generate x number of columns. I am then looking to have those summed up, but since the number of columns is dynamic, I don't know how to sum these in a formula array.
I tried to use a for loop, but I don't know how that gets integrated into the code within the formula array code. I am new to programming and have found success in reverse engineering all the code after using the macro recorder, but I don't know the specific rules to dictate how to make this code work.
This is the relevant code I have so far. M2 is where the user inputs the number of tasks. The code below only counts the kth row, but I need the formula to count from the kth row, to the kth-1 row, .... to 1.
Dim RowCount As Integer
RowCount = Selection.Rows.Count
Dim i, j, k As Integer
k = Range("M2").Value
Range(Cells(11, k + 13), Cells(11 + RowCount - 1, k + 13)).Select
Selection.FormulaArray = "=sum(RC[-" & k & "]:R[" & RowCount - 1 & "]C[-" & k & "],RC[-1]:R[" & RowCount - 1 & "]C[-1])"
Is there a way for me to either use a for loop within this so that I can sum as follows:
Selection.FormulaArray = "=sum(RC[-" & k & "]:R[" & RowCount - 1 & "]C[-" & k & "], ... ..., RC[-" & 1 & "]:R[" & RowCount - 1 & "]C[-" & 1 & "])"
Since the number of columns is dynamic, I can't manually code each of the columns summing in the array.
I know that somehow it has to be a for loop as follows:
Dim a As Integer
for a = k to 1
***SEE PRINT SCREEN ATTACHED FOR AN IDEA OF WHAT THE CODE IS TRYING TO DO
Thanks for everyone's assistance in advance!!!!
Bookmarks