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

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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 ?
                try to change range("D&num:I&num").Select to range("D" & num ":I" & num).Select and range("I & num").Activate to range("I" &  num).Activate
– Dmitry Pavliv
                Jan 7, 2014 at 10:07
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
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.