Get all Max value in all sheets in Column A located in a dynamic range

Question

I have written a code to do the specific task:

1. Go through each sheet and get the MAX of the last 365 rows in Column E.

2. The range is dynamic because each sheet have different number of rows. So I decided to use this codes:

   Sheets(Cells(i, 1).Value).Range("E1048576").End(xlUp).Offset(-365, 0).Address
   Sheets(Cells(i, 1).Value).Range("E1048576").End(xlUp).Address
   

   so it will return the (a) Last row minus 365 rows (b) Last Row resulting to Range(a to b)

3. I added the WorksheetFunction.Max function to get the max number.

4. My code is inside a for loop which gets the sheet names in Column A.

   Private Sub tester()
   
       Dim i As Integer
   
       LastRow = Sheets("Stocks Summary").Cells(Rows.Count, 1).End(xlUp).Row
   
       For i = 2 To LastRow
   
       Cells(i, 13).Value = WorksheetFunction.Max(Range(Sheets(Cells(i, 1).Value). _
           Range("E1048576").End(xlUp).Offset(-365, 0).Address & ":" & _
           Sheets(Cells(i, 1).Value).Range("E1048576").End(xlUp).Address))
   
       Next i
   
   End Sub
   

My error:

Everytime I run the macro, it returns 0. I don't understand why and what went wrong.
If somebody could help me with the code, I will truly appreciate it.

Answer 1

Private Sub tester()

    Dim i As Long, LastRow as Long, c As Range
    Dim shtSumm As Worksheet, rng as Range

    Set shtSumm = Sheets("Stocks Summary")

    LastRow = shtSumm.Cells(Rows.Count, 1).End(xlUp).Row

    For Each c in shtSumm.Range("A2:A" & LastRow).Cells

        Set rng = Sheets(c.Value).Cells(Rows.Count,1).End(xlUp). _
                      Offset(-364, 0).Resize(365, 1)

        c.EntireRow.Cells(13).Value = Application.Max(rng)

    Next c

End Sub

Answer 2

The only obvious problem is referencing. To learn more about it, check this out.
Not really about avoiding the use of Select but on how to reference objects.
Anyhow, you can make your code simpler by working directly on all the sheets.
Something like below:

Sub contract()
    Dim ws As Worksheet, oWS As Worksheet
    Dim r As Range
    Dim olr As Long, lr As Long, i As Long

    '/* this is where you get sheet list and output max */
    Set oWS = ThisWorkbook.Sheets("Stocks Summary")
    olr = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row

    For i = 2 To olr
        '/* set the worksheet */
        Set ws = ThisWorkbook.Sheets(oWS.Cells(i, 1).Value2)
        With ws
            lr = .Range("A" & .Rows.Count).End(xlUp).Row
            '/* added an if to make sure, you have 365 rows
            'else it will error out */
            If lr >= 365 Then
                '/* you only offset by -364 rows,
                'then use resize to select up to 365 rows below it */
                Set r = .Range("A" & .Rows.Count).End(xlUp).Offset(-364).Resize(365)
            Else
                '/* set the range up to the max last row only */
                Set r = .Range("A2:A" & lr)
            End If
        End With
        '/* output the max, or the min for that matter */
        oWS.Cells(i, 13).Value2 = Application.Max(r)
    Next
End Sub