Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loops
I am trying to create a macro from scratch that can tell me what value is the
maximum value in a column of values and then what the value is in the cell to the left of that max value. I thought a for loop would work for the max value part but i dont know how to get it to do that last part. Any help would be great, I am not good at programming. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loops
you don't really need a macro to do what you're describing. assuming your
potential max value is in column B this formula would return the max: =MAX(B:B) and assuming the max value cannot occur in the column more than once, this would return the value to the left of it: =INDIRECT(ADDRESS(MATCH(MAX(B:B),B:B,0),1)) "blazafan7" wrote: I am trying to create a macro from scratch that can tell me what value is the maximum value in a column of values and then what the value is in the cell to the left of that max value. I thought a for loop would work for the max value part but i dont know how to get it to do that last part. Any help would be great, I am not good at programming. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loops
The below returns the values from ColA for the max value in ColB
MsgBox Range("A" & WorksheetFunction.Match( _ WorksheetFunction.Max(Range("B:B")), Range("B:B"), 0)) -- Jacob "B Lynn B" wrote: you don't really need a macro to do what you're describing. assuming your potential max value is in column B this formula would return the max: =MAX(B:B) and assuming the max value cannot occur in the column more than once, this would return the value to the left of it: =INDIRECT(ADDRESS(MATCH(MAX(B:B),B:B,0),1)) "blazafan7" wrote: I am trying to create a macro from scratch that can tell me what value is the maximum value in a column of values and then what the value is in the cell to the left of that max value. I thought a for loop would work for the max value part but i dont know how to get it to do that last part. Any help would be great, I am not good at programming. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loops
On 5 apr, 22:58, blazafan7
wrote: I am trying to create a macro from scratch that can tell me what value is the maximum value in a column of values and then what the value is in the cell to the left of that max value. *I thought a for loop would work for the max value part but i dont know how to get it to do that last part. *Any help would be great, I am not good at programming. Hi Blazafan7 If you want the result to be shown in your sheet, you can use some combined worksheet functions: =OFFSET(x,MATCH(MAX(y:z),y:z,0)-1,0) Whe x = the to the left of the first cell with values to look in y = the first of the cells with values to look in z = the last of the cells with values to look in E.G. =OFFSET(A1,MATCH(MAX(B1:B20),B1:B20,0)-1,0) HTH, Wouter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops | Excel Programming | |||
Do loops within Do loops | Excel Programming | |||
Loops | Excel Programming | |||
for each loops | Excel Programming | |||
Do Loops | Excel Programming |