Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding maximum, minimum in a range consists both Positive and Negative numbers | Excel Worksheet Functions | |||
Finding a maximum with VLOOKUP | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | Excel Worksheet Functions | |||
Finding Maximum | Excel Worksheet Functions |