Cell formula using a variable

Question

I'm using the Excel VBA Editor (I have both Excel 2007 and Excel 2016). I have a variable parameter i, all the others are fixed. Could you please say me how I can put a formula in a cell Cells(i, 2)?

1. using variables from my macro (j1, j2, i1)
2. using variables from my worksheet (the cells J1, J2, C[-1])

C[-1] being the cell left of Cells(i, 2) eg. Cells(i, 1)?

Thans a lot, Eduard

Answer 1

Try this:

Sub date_add()
Dim i As Long
Dim dt As Worksheet

 Set dt = ThisWorkbook.Worksheets("Date")
 With dt
 lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
 For i = 1 To lastRow
     .Cells(i, 2).Formula = "=DATE(J1,J2,C" & i & ")"
 Next i
End With
End Sub

Where you input Year on J1, Month on J2 and the numbers of dates on column C