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

--
twinklejm
-----------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...fo&userid=2708
View this thread: http://www.excelforum.com/showthread.php?threadid=46941

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

Sub maxNum()

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

End Sub

--


Gary


"twinklejmj" wrote
in message ...

Hi,

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

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

Could any one please help?

Thanks.

Twinkle


--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile:
http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=469413

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


--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=469413

in B83 put in the formula

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

--
Regards,
Tom Ogilvy

"twinklejmj" wrote
in message ...

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


--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile:
http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=469413