Recording Macro -- find max value --- create chart
I am trying to record a macro that finds the maximum value in a gri file. Basically, I have this huge worksheet, with lots and lots of column and rows (maybe 1000x100). I have more than a thousand worksheets, so need to create a macro! - I need to find the max value in the worksheet (cell location change per worksheet, obviously). - I need to take the row that the max value cell is located. - Take this row and set it as the y-values for a chart, and se 1,2,3...n for x-values for the chart - Repeat steps above to find the column containing the same max valu cell... create y-values with this and 1,2,3...n for x-values. How do I do this?? ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Recording Macro -- find max value --- create chart
Hi nkob,
You can use WorksheetFunction.Max and Find method. Question: Doesn't the number inputted overlap? Sub Test() Dim rngTarget As Range, rngFound As Range, firstAddress As String Set rngTarget = Range(Cells(1, 1), Cells(1000, 100)) Set rngFound = rngTarget.Find(WorksheetFunction.Max(rngTarget), , , xlWhole) If Not rngFound Is Nothing Then firstAddress = rngFound.Address Do MsgBox rngFound.Address(0, 0) Set rngFound = rngTarget.FindNext(rngFound) Loop While Not rngFound Is Nothing And rngFound.Address < firstAddress End If Set rngTarget = Nothing: Set rngFound = Nothing End Sub Colo "nkob" wrote in message ... I am trying to record a macro that finds the maximum value in a grid file. Basically, I have this huge worksheet, with lots and lots of columns and rows (maybe 1000x100). I have more than a thousand worksheets, so I need to create a macro! - I need to find the max value in the worksheet (cell location changes per worksheet, obviously). - I need to take the row that the max value cell is located. - Take this row and set it as the y-values for a chart, and set 1,2,3...n for x-values for the chart - Repeat steps above to find the column containing the same max value cell... create y-values with this and 1,2,3...n for x-values. How do I do this??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com