find max value in multi worksheets
 

I would like to find the max value in a specific range of multi-worksheets in
a workbook.
I try to select the range of mult-worksheets but I am not able to get the
max value for below coding, please advise me what's the problem and how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double

For Each wks In Worksheets
answer = Application.WorksheetFunction.Max(wks.Range("A1:A1 0"))
If answer totalAnswer Then
totalAnswer = answer
End If
Next wks

Range("f2").Value = answer

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"sau" wrote in message
...
I would like to find the max value in a specific range of multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to get the
max value for below coding, please advise me what's the problem and how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
I would like to find the max value in a specific range of multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to get the
max value for below coding, please advise me what's the problem and how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a cell.

"Tom Ogilvy" wrote:

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
I would like to find the max value in a specific range of multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to get the
max value for below coding, please advise me what's the problem and how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"), 0))
End If
Next wks

Range("f2").Value = rng.Address(, , , True)

End Sub






--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"sau" wrote in message
...
Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a cell.

"Tom Ogilvy" wrote:

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
I would like to find the max value in a specific range of
multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to get
the
max value for below coding, please advise me what's the problem and
how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Range("f2").Value = rng.Address(, , , True)
The above coding will show the maxvalue address. That's great!! However, I
hope it could stop at the maxvalue cell. If above coding showing "sheet3!a9",
the cursor will stop at sheet 3 cell a9 (Application.Goto
Reference:="Sheet3!a9")

"Bob Phillips" wrote:

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"), 0))
End If
Next wks

Range("f2").Value = rng.Address(, , , True)

End Sub






--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"sau" wrote in message
...
Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a cell.

"Tom Ogilvy" wrote:

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
I would like to find the max value in a specific range of
multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to get
the
max value for below coding, please advise me what's the problem and
how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

find max value in multi worksheets
 

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"), 0))
End If
Next wks

Application.GoTo rng, True

End Sub

--
Regards,
Tom Ogilvy


"sau" wrote in message
...
Range("f2").Value = rng.Address(, , , True)
The above coding will show the maxvalue address. That's great!! However,
I
hope it could stop at the maxvalue cell. If above coding showing
"sheet3!a9",
the cursor will stop at sheet 3 cell a9 (Application.Goto
Reference:="Sheet3!a9")

"Bob Phillips" wrote:

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"),
0))
End If
Next wks

Range("f2").Value = rng.Address(, , , True)

End Sub






--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"sau" wrote in message
...
Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a
cell.

"Tom Ogilvy" wrote:

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
I would like to find the max value in a specific range of
multi-worksheets
in
a workbook.
I try to select the range of mult-worksheets but I am not able to
get
the
max value for below coding, please advise me what's the problem
and
how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub