locating the cell having maximum value

[twinklejmj]

Hi,

I have to locate the cell which has the maximum value (values rang
from 1 to 5, user can input vary from 1 entry to 5 entries) in
ragne of ("L26:L38") and copy the content of another cell which ha
offset of(0,-10) from the maximum value cell. Then I have to paste i
in ("B83").

This code is a small part of a long macro. So I specifically need VB
code.

Could any one please help?

Thanks.

Twinkl

 

[Gary Keramidas]

something simple like this, or do you need something more elaborate?

Sub maxNum()

Range("b83").Formula = "=maxa(L26:L38)"

End Sub

 

[twinklejmj]

Thanks, Gari.

But I fear that it is not enough. I don't want to copy the max value
rather I have to locate the max value cell. Then go to another cell in
the same row - offest from the max value cell by (0,-10) and copy the
content of that cell. The content of that cell is text.

Could any one please help?

I just reprhase my need:

I have to locate the cell containing maximum value in the range
("L26:L38"). This range contains user input, which can vary from
minimum one entry (in any one cell in L26:L38) to maximum 5 entries(in
any 5 different cells in L26:L38) . I have to locate (not copy) the
maximum value cell among the cells with user entries. Then go to
another cell in the same row but a different column (column-"B"). Copy
the text content given in that cell in "B". Then paste that selected
text in ("B83").


Thanks a lot.

Twinkle

 

[Tom Ogilvy]

in B83 put in the formula

=Index(B26:B38,match(max(L26:L38),L26:L38,0),1)