How do I Offset Rows correctly to Find the highest number in a Column in VBA?-CodePudding

I'm trying to run this code in VBA to find out which row has the highest number in column 'A'? But it's not working. Can someone help please? Below is the Code:

Sub ForNextDemo()
Dim MaxVal As Double
Dim Row As Long

MaxVal = WorksheetFunction.Max(Range("A:A"))
For Row = 1 To Rows.Count

If Range("A1").Offset(1, 0).Value = MaxVal Then
Range("A1").Offset(1, 0).Activate

MsgBox "Maximum Value is in" & Row

Exit For
End If
Next Row
End Sub

CodePudding user response：

Your code fails because you check always the same cell. No matter which value row has, Range("A1").Offset(1, 0) will always check cell A2 (1 row below A1)

What you mean is probably something like Range("A1").Offset(row, 0)

However, there is a much easier (and faster) way to get the row with the maximum value, using the Match-function.

An advice: You should tell VBA always which sheet is should use. When you write Range(A1), it will use the current active sheet. This is not always what you want. Instead, use for example ThisWorkbook.Sheets(1) (first sheet of the workbook where the code is stored). You can also use the sheet name, eg ThisWorkbook.Sheets("Sheet1")

Dim MaxVal As Double
Dim Row As Long

With ThisWorkbook.Sheets(1)
    Dim r As Range
    Set r = .Range("A:A")
    MaxVal = WorksheetFunction.max(r)
    Row = WorksheetFunction.Match(MaxVal, r, 0)
    Debug.Print "The maximum value is " & MaxVal & " and it is found in row " & Row
End With

CodePudding user response：

Get the Maximum and the Row of Its First Occurrence

You can avoid the loop if there are no error values in the column.

Sub ForNextDemo()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim MaxVal As Variant ' could be an error value
    Dim MaxRow As Long
    
    With ws.Range("A:A")
        
        If Application.Count(.Cells) = 0 Then
            MsgBox "There are no numbers in the column.", vbCritical
            Exit Sub
        End If
        
        MaxVal = Application.Max(.Cells)

        If IsError(MaxVal) Then
            MsgBox "There are error values in the column.", vbCritical
            Exit Sub
        End If
        
        MaxRow = Application.Match(MaxVal, .Cells, 0)

        ' Select and scroll to the first 'max' cell.
        Application.Goto .Cells(MaxRow), True
    
    End With
    
    MsgBox "The maximum value is " & MaxVal & "." & vbLf _
        & "Its first occurrence is in row " & MaxRow & ".", vbInformation
    
End Sub