Formula array summing a dynamic range of columns

**UMBiii** · 02-04-2014, 04:03 PM

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.Print screen.docx

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!!!!

**Tsjallie** · 02-08-2014, 05:07 PM

You could give all the task cells on one row a name, let's say "Tasks".
This name can then be referenced in vba with range("Tasks"). So when inserting or deleting tasks you can adjust the number of columns that range spans accordingly.
Then you can use this formula to sum the number of tasks:

Formula:

Please Login or Register  to view this content.

Make sure the name (or range) has references rows relatively like: $B6:$H6. This will sum the tasks in column B-H on row 6. And should be in cell I6 (according to your example).

No need to construct or even use array formulas on the fly.

BTW: if you need any additional help, please know that after next Sunday I will be away for the next 3 weeks and not able to answer your posts.