Solved: vary column

[adygelber]

Hello,

I have the following script:


For i = 1 To nrFTE
For j = 0 To nrCat
maxRow = Range("AB7").Offset(nrAtt, 0).Row
totform = "=SUM(AB7:AB" & maxRow - 1 & ")"
Range("AB" & nrAtt + 7).Offset(0, (i - 1)) = totform
Next
Next


All I want to do is to set the column as a variable in the SUM formula...practically I want to have a variable number of sums in which the column changes (ex. AB7:AB23, AC7:AC32, AD7:AD32, etc.).

Any ideas?

Thank you for your support!

[Teeroy]

The following chages to your script will make everything relative to a range object (rFirstCell) rather than a cell position (Untested).

For i = 1 To nrFTE
Set rFirstCell = Range("AB7")
sCol = Split(rFirstCell.Address, "$")(1)
For j = 0 To nrCat
maxRow = rFirstCell.Offset(nrAtt, 0).Row
totform = "=SUM(" & rFirstCell.Address & ":" & sCol & maxRow - 1 & ")"
Range(sCol & nrAtt + 7).Offset(0, (i - 1)) = totform
Next
Next

[adygelber]

To be more clear I attached the file. As you can see the formula it's ok on AB33 because it makes the sum of the cells AB7:AB32. But in the cell AC33 I have the same formula instead of AC7:AC32 and so on for the rest of cells AD33, AE33, etc.
This is the reason why I asked to vary the column letters and to keep the number, because I want to multiply horizontally the formula.

Can you help me?

PS: The code which does not work is written in brackets "----------------------"

Thanks! :yes

[Teeroy]

Sorry I mustn't have made myself clear, you need to move the range object as you go. Now that I can see what your variables do you can replace:
'--------------------------
For i = 1 To nrFTE * nrCat
For j = 0 To nrCat
maxRow = Range("AB7").Offset(nrAtt, 0).Row
totform = "=SUM(AB7:AB" & maxRow - 1 & ")"
Range("AB" & nrAtt + 7).Offset(0, (i - 1)) = totform
Next
Next
'--------------------------
With

Dim rng As Range
Dim rng1 As Range

Set rng1 = Range("AB7").Resize(1, nrFTE * nrCat - 1)
For Each rng In rng1
rng.Offset(nrAtt, 0).Formula = "=sum(" & _
Range(rng, rng.Offset(nrAtt - 1, 0)).Address & ")"
Next

[p45cal]

…or by one line:Range("AB7").Offset(nrAtt).Resize(, nrFTE * nrCat).FormulaR1C1 = "=SUM(R[-" & nrAtt & "]C:R[-1]C)"

[p45cal]

groan.. cross-posts:
http://www.ozgrid.com/forum/showthread.php?t=170856
and
http://www.excelforum.com/excel-programming-vba-macros/868804-vary-column.html?p=2971903#post2971903