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/