Solved: Find Max Value across Wkshts and display max in alert

[kilbey1]

I have started this basic code that attempts to give me the max value throughout all worksheets, but have not defined a range (should be column A across all worksheets). An alert appears on each sheet, and I'd like it to add the largest value into an array and only output the max value from all worksheets once in a single alert, after having reached the last worksheet.


Sub ListMaxValuesFromSheet()
For Each ws In ActiveWorkbook.Worksheets
With ws
MsgBox WorksheetFunction.Max(.UsedRange), vbExclamation
ActiveSheet.Next.Select
End With
Next
End Sub


The idea is to find the last assigned bug tracker ID across all worksheets and have it displayed so the user knows where they left off.

[mikerickson]

If you just want the Max value, this will get it without looping.With ThisWorkbook
MsgBox Evaluate("MAX(" & .Sheets(1).Name & ":" & .Sheets(.Sheets.Count).Name & "!A:A)")
End With
If you want to fill an array and report the maximum value
Dim SheetWiseMax() As Double
Dim i As Long

With ThisWorkbook
ReDim SheetWiseMax(1 To .Sheets.Count)
For i = 1 To .Sheets.Count
SheetWiseMax(i) = Application.Max(.Sheets(i).Range("A:A"))
Next i
End With

MsgBox Application.Max(SheetWiseMax) & " is the largest Column A value in the whole workbook."

[kilbey1]

Wow, is that it? So something like so:


Sub ListMaxValuesFromSheet()
Dim ws As Worksheet
'For Each ws In ActiveWorkbook.Worksheets
With ThisWorkbook
MsgBox Evaluate("MAX(" & .Sheets(1).Name & ":" & .Sheets(.Sheets.Count).Name & "!A:A)")
End With
'Next
End Sub


However, I get a type mismatch. My guess is this is because I have text and numbers in the same column. I only want to get the max value where a number is present.

[mikerickson]

That code works for me even with text in A:A, but Evaluate is quirky, perhaps this will work better on your system.
With ThisWorkbook
MsgBox Evaluate("=MAX(" & .Sheets(1).Name & ":" & .Sheets(.Sheets.Count).Name & "!A:A)")
End With

If there are error values in A:A (eg. #DIV/0) that will cause the type mismatch.

Also, it looks like you were posting while I was editing in the Array getting version above.

[kilbey1]

Yes, I noticed that too. :)

The second solution gave me what I needed. For some reason, I still got a type mismatch on the evaluate, but option 2 works flawlessly.

Thanks!