Excel VBA Trying to write a "MAX" formula to Cells with different Ranges with For-Loop

Question

I am trying to make VBA write a formula into different cells that will find the maximum value for a Range decided by some variables. My variables I and J are (numbers/Integers).

Here is my code.

Sub AddMAX()
    Dim I As Integer
    Dim J As Integer
    Dim L As Integer

    I = InputBox("Number of columns to check max value")
    J = InputBox("Number of Rows to add formula inn and find max value of that row")

    For L = 5 To 4 + J
        Worksheets(1).Cells(L, 4 + I).Formula = "=" & Max(Range(Cells(L, 4), Cells(L, 3 + I)))
    Next L
End Sub

Have tried to re-write the second part (part behind the equal sign) several times. Usually I get the message Compile error: Sub or Function not defined and it marks the "Max". I thought Max (also tried with big letters) was an in-built function like SUM and so on.

I'm trying to make it write an Excel formula like this into the cells:

For I=2 and J=3:

Cell F5: =MAX(D5:E5)
Cell F6: =MAX(D6:E6)
Cell F7: =MAX(D7:E7)

i.e. I want a formula in the cells like I had wrote it in the cells manually to calculate max value, so that if the value in Cells D5, to D7 and E5 to E7 change, the new max value will be found without any scripts having to run.

Let me know if something is unclear.

You do not need a loop. You can enter the formula in the entire range in 1 go. For example `.Range("F5:F" & 4 + J).Formula = "=Max(D5:E5)"` — Siddharth Rout, Apr 05 '19 at 07:24
Thank you for your answer. But I think I need the loop, because the Cells change depending on what input you do in the "InputBox"'es. i.e. the range for cell formula change depending on how many columns you have. So the formula can be `"=Max(H5:M5)"` or it can be `"=Max(B5:X5)"` and so on. It must be acceptable for different setups, ie different amounts of columns to be checked for MAX value. — GingerBoy, Apr 05 '19 at 07:29
So your formula would be different depending on the input, but you still don't need a loop and still can assign it in one operation. — GSerg, Apr 05 '19 at 07:39
@Siddharth Rout What I don't understand then, is how to make the formula dynamic. The Range `Range("F5:F" & 4 + J).Formula` should be dynamic to left and right, aswell as numbers of rows (which you already have made it dynamical for). Further the resulting formula in each cell should correspond to the same row as the `"=Max(D5:E5)"`. I.e `"=Max(WX:WY)"` must refer to range of values in same row. I'm probably not seeing something you guys see, but hope you can try explaining more to me :-) Beginner in VBA so far, but learning more. — GingerBoy, Apr 05 '19 at 08:55

GSerg · Accepted Answer · 2019-04-05T09:40:03.640

You should not be putting Range and Cells in a formula string, they mean nothing to the Excel formula engine. You need the Address of the cells:

Dim I As Long
Dim J As Long
Dim L As Long

I = InputBox("Number of columns to check max value")
J = InputBox("Number of Rows to add formula inn and find max value of that row")
L = 5

With Worksheets(1)
  .Range(.Cells(L, 4 + I), .Cells(4 + J, 4 + I)).Formula = "=MAX(" & .Cells(L, 4).Address(False, False) & ":" & .Cells(L, I + 3).Address(False, False) & ")"
End With

The formula is actually the same for all cells, which is why it is possible to assign it in one assignment for the entire range. It looks different in the A1 reference notation, but if you switch to R1C1 in the Excel settings, you will see they are the same. Which also means it is easier to create that formula using the R1C1 notation in the first place:

Dim I As Long
Dim J As Long
Dim L As Long

I = InputBox("Number of columns to check max value")
J = InputBox("Number of Rows to add formula inn and find max value of that row")
L = 5

With Worksheets(1)
  .Range(.Cells(L, 4 + I), .Cells(4 + J, 4 + I)).FormulaR1C1 = "=MAX(RC[-" & I & "]:RC[-1])"
End With

But it would appear to me that you should instead use the Excel interface the intended way. Select the cells in which the MAX formula should be. Keeping the entire range selected, put the MAX formula into any of its cells as if you were creating it for just that cell, but instead of pressing Enter, press Ctrl+Enter.

Thanks for the in-depth explanation! This was exactly what I was looking for. It is nice that you also show easier methods to define cells/ranges. Have a great weekend! — GingerBoy, Apr 05 '19 at 09:55

FunThomas · Answer 2 · 2019-04-05T09:07:56.260

You have to be careful to distinct between the part that is seen by VBA and the final formula.

If you write

Worksheets(1).Cells(L, 4 + I).Formula = "=" & Max(Range(Cells(L, 4), Cells(L, 3 + I)))

Max (and all the following stuff) is seen by the VBA-interpreter, not Excel. But there is no Max-function, and you get an (compiler)-error.

If you write

Worksheets(1).Cells(L, 4 + I).Formula = "=Max(Range(Cells(L, 4), Cells(L, 3 + I)))"

the VBA-interpreter sees the whole stuff as a string. It cannot take care about variables like L or I because is doesn't see them. So you end up with a formula that is exactly like you write it - and Excel (not VBA) will show you an error because it doesn't understand L or I.

What you need is a statement (in VBA) that creates a string that contains the actual values of your variables, and assign it to the cell.formula. I strongly advice that you first assign this to a string variable - it makes debugging much easier:

Dim formula As String
formula = "=Max(Range(Cells(" & L & ", 4), Cells(" & L & ", 3 + " & I & ")))"
Debug.Print formula
Worksheets(1).Cells(L, 4 + I).Formula = formula

Update: Sorry, I haven't looked to the content of the formula at all, of course the Range and Cells-objects are VBA objects. What you need in your formula is the address of the range, so change the line to

formula = "=MAX(" & Range(Cells(L, 4), Cells(L, 3 + i)).Address & ")"

Now VBA will create a Range and put the address into the formula string.

Thanks for the explaination. It seems It kinda works, but It won't add and calculate. In the cells the formula looks like a string: `= MAX((Cells(5; 4): Cells(5; 3 + 2)))` It will not calulate, it only shows #NAME? on the cell that should show the maximum value. When I check the formula in the cells, it says `= MAX((Cells(5; 4): Cells(5; 3 + 2)))`. Do you know what could be the problem. Is it the defenition for `Dim formula As String`? Cheers for any help — GingerBoy, Apr 05 '19 at 08:45

Excel VBA Trying to write a "MAX" formula to Cells with different Ranges with For-Loop

2 Answers2

Linked