Finding max value
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 !!! :) |
Finding max value
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 !!! :) |
Finding max value
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 !!! :) |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com