VBA - Loop through worksheets to find max value in user-selected range

3
votes

I'm trying to find the maximum value for each worksheet in my workbook that is dependent on a user-selected range. I want the user to select a range just once and find the max for each worksheet based on that range.

In other words, say the user selects the range B1:B100. The macro should loop through each sheet and find the max value in each sheet's B1:B100 range.

Ultimately, each worksheet has a set of data points that I want to divide by the max value found in the user-selected range.

Here is my code (well, the relevant parts):

Sub Plot()

Dim i, ws_count As Integer
Dim userng, brng As Range
Dim maxval As Double

On Error GoTo Canceled

Set userng = Excel.Application.InputBox("Please input range.", "Input", Selection.Address, , , , , 8)

ws_count = ActiveWorkbook.Worksheets.Count

  For i = 1 To ws_count

    Worksheets(i).Activate

    maxval = userng.Cells(1, 1).Value
      For Each Cell In userng.Cells
        If Cell.Value > maxval Then
          maxval= Cell.Value
        End If
      Next Cell

    Set brng = Worksheets(i).Range("B1:B1869") ''brng will always be B1:B1869 for each worksheet

'Each cell in B1:B1869 should be divided by maxval.
      For Each Cell In brng
        Cell.Value = Cell.Value / maxval
      Next Cell

  Next i

Canceled:
End Sub

Unfortunately, the macro only finds the max value for the first worksheet and applies this value to all the other worksheets. Ideally, each worksheet would be divided by its unique maxval.

I'd certainly appreciate any help that is given!

2
excelvbafor-loopinputworksheet

2 Answers

1
votes

You can get a user range directly from the user and then apply it as below:

Sub Test()
Dim rng1 As Range
Dim ws As Worksheet

On Error Resume Next
Set rng1 = Application.InputBox("Pls select range to examine (on all sheets)", "User Input", Selection.Address, , , , , 8)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub

For Each ws In ActiveWorkbook.Sheets
 Debug.Print ws.Name, Application.WorksheetFunction.Max(ws.Range(rng1.Address))
Next

End Sub
0
votes

Here is an example of how you can get the max value for the current range in each sheet:

Sub MaxInSheets()
    Dim MyRange As String, WS As Worksheet
    MyRange = Selection.Address
    For Each WS In Worksheets
        MsgBox WS.Name & " - " & WorksheetFunction.Max(WS.Range(MyRange))
    Next
    MsgBox MyMaxValue
End Sub

Post back if you need help to implement that into your own code.

I tried making MyRange a range but it didn't like it, I am not 100% sure but I think it may retain the sheet as a reference in the range? someone with more knowledge of the object will be able to confirm this though.