Finding Maximum Within Range (Looped)

[Jacob]

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

 

[Die_Another_Day]

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]

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]

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]

thanks! that was a big help.

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

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.

 

[Tom Ogilvy]

for your example,
why not just do

Range("E5:E6").formula = "=A1 + A2"

--
Regards,
Tom Ogilvy

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

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]

I was attempting to show what can be done with the FormulaR1C1 style as
it can be quite powerful depending on what needs accomplished. I know
it was a weak analogy but I tried. The main point in this case was that
he had column numbers not letters.

Charles Chickering

for your example,
why not just do

Range("E5:E6").formula = "=A1 + A2"

--
Regards,
Tom Ogilvy

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

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

 

[Tom Ogilvy]

Just an observation, but maybe you should use the term relative rather than
dynamic since that is called relative addressing.

Also, for the particular formula, perhaps using the technique in your
suggested approach

Cells(19, Count2 + 2).FormulaR1C1 = "=Max(R21C[-2]:R100C[-2])"

would be more likely to show the strength of R1C1 notation. (since this
would be harder using A1)

--
Regards,
Tom Ogilvy

I was attempting to show what can be done with the FormulaR1C1 style as
it can be quite powerful depending on what needs accomplished. I know
it was a weak analogy but I tried. The main point in this case was that
he had column numbers not letters.

Charles Chickering

for your example,
why not just do

Range("E5:E6").formula = "=A1 + A2"

--
Regards,
Tom Ogilvy

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