Copy a Formula in a Macro



Ok, so I have this macro that makes copies of Sheet1 based on a list of
values in Sheet_2. Works great. However, I need to change a cell so that it
not only copies over the value, but inserts a formula.
The macro lines that creates the new sheets is as follows:

Set TemplateWks = Worksheets("Sheet1")
Set sh = Worksheets("Sheet1")
Set ListWks = Worksheets("Sheet_2")
With ListWks
Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)

On Error Resume Next
With ActiveSheet
.Name = myCell.Value
.Range("B4").Value = myCell.Value
.Range("C5").Value = myCell.Offset(0, -1).Value '****<= This is the
line I need to change
End With

How do I insert a quick formula into the cell "C5" so that it rounds down
the number? I am thinking that it should be something like
.Range("C5").Value = myCell.Offset(0,-1).Value
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it inserts

Per Jessen

Use this line:

..Range("C5").Value = WorksheetFunction.RoundDown(myCell.Offset(0, -1).Value)



I changed it to the following and it worked.

ActiveCell.FormulaR1C1 =
WorksheetFunction.RoundDown((myCell.Offset(0, -1)), 0)

Thanks for the help.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question