Finding Maximum Within Range (Looped)
 

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

Finding Maximum Within Range (Looped)
 

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

Finding Maximum Within Range (Looped)
 

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

Finding Maximum Within Range (Looped)
 

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

Finding Maximum Within Range (Looped)
 

thanks! that was a big help.


Die_Another_Day wrote:
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

Finding Maximum Within Range (Looped)
 

for your example,
why not just do

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

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

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

Finding Maximum Within Range (Looped)
 

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

Tom Ogilvy wrote:
for your example,
why not just do

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

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

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

Finding Maximum Within Range (Looped)
 

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



"Die_Another_Day" wrote in message
oups.com...
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

Tom Ogilvy wrote:
for your example,
why not just do

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

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

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