Cell formula using a variable

1
votes

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

2
excelexcel-r1c1-notationvba
stackoverflow.com/questions/18807505/… and stackoverflow.com/questions/18744537/… - danieltakeshi
stackoverflow.com/questions/18807505/…, 2).Formula = "=Date("&j1&";"&j2&";C[-1])" - eddie
based on stackoverflow.com/questions/18807505/…, I would write Cells(i, 2).Formula = "=Date("&j1&";"&j2&";C[-1])" but it doesn't work - eddie
You need to make it on Excel VBA, so you should create somethink like: Worksheets("Sheet_NAME").Range(Cells(i,2)).Formula = "=DATE(J1;J2;C"& i & ")" ---- and use a For i=1 to Last_Row ps.: Syntax: DATE(year,month,day) and the formula will be insert on the line 'i' and column B and the DATE fomula will get data from J1;J2; column C line 'i' - danieltakeshi
it's exactly what I did. The compilator just says "compilation error: syntax error" and points to "," - eddie

2 Answers

1
votes

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

Like this image

0
votes

Part of the answer. Say cell A1 contains the value 2. Running this:

Sub eddie()
    Dim i As Long, s As String
    i = Range("A1").Value
    s = "=DATE(20,20,20)"
    Cells(i, 2).Formula = s
End Sub

will place the formula in cell B2

enter image description here