Prev: Insert formula even cells with data below it
Next: Copypicture from Excel to PowerPoint
From: blazafan7 on 5 Apr 2010 16:58
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.
From: B Lynn B on 5 Apr 2010 17:28
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.
From: Jacob Skaria on 5 Apr 2010 21:45
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.
From: Wouter HM on 6 Apr 2010 15:44
On 5 apr, 22:58, blazafan7 <blazaf...(a)discussions.microsoft.com>
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)

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
 | 
Pages: 1
Prev: Insert formula even cells with data below it
Next: Copypicture from Excel to PowerPoint