VBA, how to make formulas visible in Excel

0
votes
Dim n As Double
n = Sh.Range("B10").Value

Dim rate As Double
rate = (datefin - datedepart) / n

Cells(13, n + 3) = datefin

For y = n - 1 To x Step -1
Cells(13, y + 3) = datefin - rate
datefin = datefin - rate
Next y

I have this simple for loop, that puts the values into the cells in excel starting from n (given by the user) and going backwards according to a rate (which is also calculated by values given by the user).

The output values I get are correct, but I would like to make my formulas visible in excel. For example, if I press on the cell D13 I would like it to show the formula I used. I tried to do it with the ActiveCell.Formula or ActiveCell.FormulaR1C1 function but since n is not always a specific number (and hence on a specific cell) I cannot find how to execute what I want.

Thank you!!!

1
excelvbarangeformulacells

1 Answers

2
votes

In general you would use .Formula to attach a formula to a cell, however if the individual components aren't actually contained in other cells of your spreadsheet the best you are going to be able to do is something like

Cells(13, y + 3).Formula = "=" & format(datefin) & " - " & format(rate)

Which if datefin is 9 and rate is 2 would put the formula "=9 - 2" in the cell.