for loops

  • Thread starter Thread starter blazafan7
  • Start date Start date
B

blazafan7

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.
 
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))
 
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))
 
I am trying to create a macro from scratch that can tell me what value isthe
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.
Click to expand...

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)

Where:
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
 
Back
Top