title: VBA - Loop through worksheets to find max value in user-selected range
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>Ultimately, each worksheet has a set of data points that I want to divide by the max value found in the user-selected range.</p>
  
  <p>Here is my code (well, the relevant parts):</p>
  
  <pre><code>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 &gt; 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
  </code></pre>
  
  <p>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 <code>maxval</code>.</p>
  
  <p>I'd certainly appreciate any help that is given!</p>