1

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

Community
  • 1
  • 1
eddie
  • 415
  • 4
  • 13
  • 1
    https://stackoverflow.com/questions/18807505/how-to-enter-a-formula-into-a-cell-using-vba and https://stackoverflow.com/questions/18744537/vba-setting-the-formula-for-a-cell – danieltakeshi Jun 21 '17 at 11:56
  • https://stackoverflow.com/questions/18807505/how-to-enter-a-formula-into-a-cell-using-vbaCells(i, 2).Formula = "=Date("&j1&";"&j2&";C[-1])" – eddie Jun 21 '17 at 12:34
  • based on https://stackoverflow.com/questions/18807505/how-to-enter-a-formula-into-a-cell-using-vba, I would write Cells(i, 2).Formula = "=Date("&j1&";"&j2&";C[-1])" but **it doesn't work** – eddie Jun 21 '17 at 12:35
  • 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 Jun 21 '17 at 12:51
  • it's exactly what I did. The compilator just says "compilation error: syntax error" and points to "," – eddie Jun 21 '17 at 13:12
  • I added an answer – danieltakeshi Jun 21 '17 at 13:32
  • I just forgot the spaces: "&i&" instead "& i & ". thanks a lot – eddie Jun 21 '17 at 13:48

2 Answers2

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

Like this image

danieltakeshi
  • 887
  • 9
  • 37
  • indeed, i had almost your formula.
    I just didn't add any spaces and used "&i&" which made the compilator bug. thanks a lot
    PS: indeed, cells(i,2) is Bi     – eddie Jun 21 '17 at 13:45
0

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99