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

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/