CODEWITHSUNDEEP
excelrowrangemaxvba
user3168477
user3168477

Reputation: 23

How to get in VBA the max value of the cells in the row range 2:60 and column range D:H

I am having an Excel sheet with integer values in 60 rows and in 7 columns. First row is the commentaire, and first three columns are the commentaires as well (I have to ommit those).

So, I would like to iterate through the row range 2:60 in column range 3:7 (D:H) to find the max value of the celles in a row. Answer goes to the celles in the column I.

What I have (simplified view):

       Test1  Test2  Test3  Test4
One    1      2      3      4
Two    5      6      7      8
Three  1      2      5      4
Four   1      7      3      4

What I would like to have:

       Test1  Test2  Test3  Test4  Max
One    1      2      3      4      4
Two    5      6      7      8      8
Three  1      2      5      4      5 
Four   1      7      3      4      7

If I create a macro manually, I receive this:

range("D2:I2").Select
range("I2").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"

range("D3:I3").Select
range("I3").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"

range("D4:I4").Select
range("I4").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"

Cool... but I would like to iterate through that with a loop.

I tried this, but doesnt work:

Sub max()
    For num = 2 To 4
        range("D&num:I&num").Select
        range("I & num").Activate
        ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"
    Next num
End Sub

Any ideas ?

Upvotes: 2

Views: 15393

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Simoco has already mentioned what the error in your code is. I would also add that you should avoid the use of .Activate. You might want to see THIS

The most simplest way would be to use this which doesn't use looping

Sub Sample()
    With ThisWorkbook.Sheets("YourSheetName")
        .Range("I2:I60").Formula = "=Max(D2:H2)"
    End With
End Sub

Upvotes: 1

Related Questions