Recording Macro -- find max value --- create chart

  • Thread starter Thread starter nkob
  • Start date Start date
N

nkob

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??
 
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
 
Back
Top