R1C1 means reference by Row # and Column #, thus letting you specify
the Column with "count2". The really cool thing with this is that by
using [] you can make the formula "dynamic".
Take for example a formula in cell E5
=R1C1 + R2C1 would be =A1 + A2
=R[-4]C[-4] + R[-3]C[-4] also =A1 + A2 and is dynamic because you can
copy it down to E6 and get:
=A2 + A3 with the exact same formula
Just something to keep in mind as sometime during programming you will
probably use it.

Charles

Jacob wrote:
The first solution did not work although I clearly made a mistake by
referring to column 0.

the second solution did work, but I'm curious...does this use cell A1
to store the number first? I don't know what the "formulaR1C1 means
exactly.

thanks so much for your help.


Die_Another_Day wrote:
I think Count2 needs to be initialized as 1
Count2 = 1
For q = 0 To span Step delta 'loop for calculating max of each column
Max = Application.Max(Cells(21, Count2), Cells(100, Count2))
Cells(19, Count2 + 2) = Max
Count2 = Count2 + 1
Next q

You can also asign a formula to the cell:
Count2 = 1
For q = 0 To span Step delta 'loop for calculating max of each column
Cells(19, Count2 + 2).FormulaR1C1 = "=Max(R21C" & Count2 &
":R100C" _
& Count2 & ")"
Count2 = Count2 + 1
Next q


Charles Chickering

Jacob wrote:
There is an error with the 3rd line of the code below, and I cannot
figure out why. I guess I don't understand how to use "Range" with
indexing very well. Is there a better way to go about this?
thanks.


Count2 = 0
For q = 0 To span Step delta 'loop for calculating max of each column
Max = Application.Max(Range(Cells(21, Count2), Cells(100, Count2)))
Cells(19, Count2 + 2) = Max
Count2 = Count2 + 1
Next q