VBA - Error 1004

Question

This line shows a runtime error 1004, but I don't understand why:

ActiveCell.Formula = "=MATCH(R" & i & "C16;R" & i & "C6:R" & i & "C15;0)"

Sub Call_Min()

    Dim i As Integer
    Dim limit As Integer
    Sheets("AUX").Activate
    limit = ActiveSheet.Range("B6").Value

    Sheets("DATA").Activate
    'ActiveSheet.Cells(6, 16).Select
    'ActiveCell.Formula = "=SUM(Range("F6:I6"))"
    For i = 6 To limit

      'MATCH(P6;F6:O6;0)
      ActiveSheet.Range("P" & i).Select
      ActiveCell.Formula = "=MIN(R" & i & "C6:R" & i & "C15)"

      ActiveSheet.Range("E" & i).Select
      ActiveCell.Formula = "=MATCH(R" & i & "C16;R" & i & "C6:R" & i & "C15;0)"
    Next i

End Sub

It should be `ActiveCell.FormulaR1C1` and not `ActiveCell.Formula`. — Ralph, Apr 11 '16 at 20:47
Change `ActiveCell.Formula` to `ActiveCell.FormulaR1C1Local ` — Scott Craner, Apr 11 '16 at 20:50

score 2 · Answer 1 · edited May 23 '17 at 12:24

The issue is that vba is very US-English centric. So either use , in place of ; or use .FormulaR1C1Local:

Sub Call_Min()
Dim i As Integer
Dim limit As Integer

limit = Sheets("AUX").Range("B6").Value

Sheets("Sheet11").Activate
'ActiveSheet.Cells(6, 16).Select
'ActiveCell.Formula = "=SUM(Range("F6:I6"))"
For i = 6 To limit

      'MATCH(P6;F6:O6;0)
      Sheets("DATA").Range("P" & i).FormulaR1C1Local = "=MIN(R" & i & "C6:R" & i & "C15)"

      Sheets("DATA").Range("E" & i).FormulaR1C1Local = "=MATCH(R" & i & "C16;R" & i & "C6:R" & i & "C15;0)"
Next i
End Sub

See here on how to avoid using .Select and why.

++ for avoiding `Select` and `ActiveCell` – Mathieu Guindon Apr 11 '16 at 21:00 — Mathieu Guindon, Apr 11 '16 at 21:00

score 1 · Answer 2 · answered Apr 11 '16 at 20:55

Sub Call_Min()

    Dim i As Integer
    Dim limit As Integer
    Sheets("AUX").Activate
    limit = ActiveSheet.Range("B6").Value

    Sheets("DATA").Activate
    'ActiveSheet.Cells(6, 16).Select
    'ActiveCell.Formula = "=SUM(Range("F6:I6"))"
    For i = 6 To limit

      'MATCH(P6;F6:O6;0)
      ActiveSheet.Range("P" & i).Select
      ActiveCell.FormulaR1C1 = "=MIN(R" & i & "C6:R" & i & "C15)"

      ActiveSheet.Range("E" & i).Select
      ActiveCell.FormulaR1C1 = "=MATCH(R" & i & "C16,R" & i & "C6:R" & i & "C15,0)"
    Next i

End Sub

Two changes were made:

Usage of FormulaR1C1 instead of Formula.
Replacing the ; in the formulas for ,.

VBA - Error 1004

2 Answers2