Fruits Mon Tues Wed Thurs Fri Sat Sun

Apple 1585 1122 1858 1989 1744 1992 989
Orange 2367 3312 1878 1066 1356 1878 2001
Watermelon 1001 1228 1355 1762 1882 1122 1311
Banana 1566 1788 1324 1333 1288 1671 1451
Mango 1021 1818 1613 1423 1902 1312 1515

Max 2367 3312 1878 1989 1902 1992 2001

I have used the following worksheet function to find the most no.of
fruits sold in each day.

MAX(B2:B6)
For e.g, On Monday, using the above function will give me value of
2367

I proceed on further to tell the worksheet to activate the cell that
contain the Max value using the below code:

Range("B:B").find(What:=[B7].Value).Activate


However, this only activate the cell that contains the value of 2367.
Is there a way to write a code which allows
the fruit which contains the max value of 2367 to be activate instead,
which in this case, the CEll "Orange"to be activate?


A million thanks !!! :)

Hi,

Here is one method that activates Orange.

Sub Paradise()

Dim iRow As Integer

iRow = Range("B:B").Find(Application.Max(Range("B:B"))).R ow

Cells(iRow, 1).Select

End Sub

Hope this helps.

Regards,
Kevin





"paradise" wrote in message
om...
Fruits Mon Tues Wed Thurs Fri Sat Sun

Apple 1585 1122 1858 1989 1744 1992 989
Orange 2367 3312 1878 1066 1356 1878 2001
Watermelon 1001 1228 1355 1762 1882 1122 1311
Banana 1566 1788 1324 1333 1288 1671 1451
Mango 1021 1818 1613 1423 1902 1312 1515

Max 2367 3312 1878 1989 1902 1992 2001

I have used the following worksheet function to find the most no.of
fruits sold in each day.

MAX(B2:B6)
For e.g, On Monday, using the above function will give me value of
2367

I proceed on further to tell the worksheet to activate the cell that
contain the Max value using the below code:

Range("B:B").find(What:=[B7].Value).Activate


However, this only activate the cell that contains the value of 2367.
Is there a way to write a code which allows
the fruit which contains the max value of 2367 to be activate instead,
which in this case, the CEll "Orange"to be activate?


A million thanks !!! :)

Range("B:B").find(What:=[B7].Value).offset(0,-1).Activate

--
Regards,
Tom Ogilvy

paradise wrote in message
om...
Fruits Mon Tues Wed Thurs Fri Sat Sun

Apple 1585 1122 1858 1989 1744 1992 989
Orange 2367 3312 1878 1066 1356 1878 2001
Watermelon 1001 1228 1355 1762 1882 1122 1311
Banana 1566 1788 1324 1333 1288 1671 1451
Mango 1021 1818 1613 1423 1902 1312 1515

Max 2367 3312 1878 1989 1902 1992 2001

I have used the following worksheet function to find the most no.of
fruits sold in each day.

MAX(B2:B6)
For e.g, On Monday, using the above function will give me value of
2367

I proceed on further to tell the worksheet to activate the cell that
contain the Max value using the below code:

Range("B:B").find(What:=[B7].Value).Activate


However, this only activate the cell that contains the value of 2367.
Is there a way to write a code which allows
the fruit which contains the max value of 2367 to be activate instead,
which in this case, the CEll "Orange"to be activate?


A million thanks !!! :)