Excel VBA insert formula with a variable into a cell

Question

I need to insert a formula using VBA into a cell but I always get a run-time error 1004. Code looks like this:

Sub save_data()

Dim source As Worksheet
Dim list As Worksheet
Dim nextRow As Integer
Dim nextId As Integer
Dim startDate As Date, endDate As Date, randDatum As Date, dateOfBirth As Date

startDate = "17/6/2018"
randDate = WorksheetFunction.RandBetween(startDate, Date)

Set source = Worksheets("Add user")
Set list = Worksheets("Users")

nextRow = list.Range("A" & list.Rows.Count).End(xlUp).Offset(1).Row
nextId = list.Range("A" & list.Rows.Count).End(xlUp).Value + 1
dateOfBirth = source.Range("F24").Value

list.Cells(nextRow, 1).Value = nextId
list.Cells(nextRow, 2).Value = source.Range("F4").Value
list.Cells(nextRow, 3).Value = source.Range("F6").Value
list.Cells(nextRow, 4).Value = source.Range("F8").Value
list.Cells(nextRow, 5).Value = source.Range("F10").Value
list.Cells(nextRow, 6).Value = source.Range("F12").Value
list.Cells(nextRow, 7).Value = source.Range("F14").Value
list.Cells(nextRow, 8).Value = source.Range("F16").Value
list.Cells(nextRow, 9).Value = source.Range("F18").Value
list.Cells(nextRow, 10).Value = source.Range("F20").Value
list.Cells(nextRow, 11).Value = source.Range("F22").Value
list.Cells(nextRow, 12).Value = source.Range("F24").Value
list.Range("M" & nextRow).Formula = "=ROUNDDOWN(YEARFRAC(" & dateOfBirth & ",TODAY(),1),0)"
list.Cells(nextRow, 14).Value = Date
list.Cells(nextRow, 15).Value = randDate
list.Cells(nextRow, 16).Value = Date - randDate
list.Cells(nextRow, 16).Value = "=TODAY()"

End Sub

I have tried Formula and FormulaLocal but result is always the same.

What is the value of the dateOfBirth variable when you break the code at this line? What is the "result" that is "always the same"? An error? Unexpected answer? Something else? — JNevill
– JNevill, Commented May 1, 2019 at 12:32
Value of the dateOfBirth is 6.7.2011. The resul is that I always get run-time error. And yes source and list are set corretly, it works in other parts of the code. — NaXXik
– NaXXik, Commented May 1, 2019 at 12:39
We need to see the full code. Your formula appears fine. Perhaps nextrow is populated with a value <1 or >1,048,576. Can only guess with this partial code — Tim Stack
– Tim Stack, Commented May 1, 2019 at 13:00

CLR · Accepted Answer · 2019-05-01 14:19:21Z

1

Let's say the date of birth is 6.7.2011.

That means, using your formula:

.Formula = "=ROUNDDOWN(YEARFRAC(" & dateOfBirth & ",TODAY(),1),0)"

it will create the formula:

=ROUNDDOWN(YEARFRAC(6.7.2011,TODAY(),1),0)

Can you see the issue now?

Try using:

.Formula = "=ROUNDDOWN(YEARFRAC(" & dateOfBirth * 1 & ",TODAY(),1),0)"

This will create the formula:

=ROUNDDOWN(YEARFRAC(40730,TODAY(),1),0)

..which while looking a little strange, should give you the right answer and not error.

answered May 1, 2019 at 14:19

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA insert formula with a variable into a cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related