CODEWITHSUNDEEP
excelvbamaxmultiple-columns
Raúl
Raúl

Reputation: 159

Find the maximum in each row

I have three columns of data:
enter image description here

I'd like to find the maximum value of each row and place it in column E on the corresponding row.
For example, for first row: 41.13, second row: 2143.404 and so on.

Dim LastRowIdent As Long, LastRowCant As Long

With Sheets("LOL")

    LastRowIdent = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("A2:A" & LastRowIdent).Copy Destination:=Sheets("Animal").Range("A5") 
    LastRowCant = .Cells(.Rows.Count, 7).End(xlUp).Row
    .Range("G2:G" & LastRowCant).Copy Destination:=Sheets("Animal").Range("B5") 
        
End With
    
Application.DisplayAlerts = False
    
With Sheets("Animal")
        
    Dim NumSheet As Long
    NumSheet = ThisWorkbook.Worksheets.Count - 6
        
    Range("F:F").Copy
    Range("G:G").Resize(, NumSheet).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
            
    Range("E:E").Copy
    vRange("F:F").Resize(, NumSheet).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
        
    Range("C:C").Copy
    Range("D:D").Resize(, NumSheet).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
        
    Dim M As Long
    For M = 1 To NumSheet + 1
        Dim LastRowPrec As Long
        LastRowPrec = ActiveWorkbook.Worksheets(2 + M).Cells(.Rows.Count, 11).End(xlUp).Row - 2
        ActiveWorkbook.Worksheets(2 + M).Range("K2:K" & LastRowPrec).Copy Destination:=.Cells(5, 2 + M)
    Next M
    
End With

Upvotes: 0

Views: 289

Answers (1)

Raúl
Raúl

Reputation: 159

Thank you to @BigBen, the line I was looking for was: Range(.Cells(5, 4 + NumSheet), .Cells(LastRowMax, 4 + NumSheet)).Formula = "=MAX(" & .Cells(5, 3).Address(False, False) & ":" & .Cells(5, 3 + NumSheet).Address(False, False) & ")"

Upvotes: 1

Related Questions