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 |
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 |