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
.Range("C5").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it inserts
it.
--
Nothing in life is ever easy - just get used to that fact.

Copy a Formula in a Macro
 

Use this line:

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

Regards,
Per

"KennyD" skrev i meddelelsen
...
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
.Range("C5").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it
inserts
it.
--
Nothing in life is ever easy - just get used to that fact.

Copy a Formula in a Macro
 

It returns a Compile Error: Argument Not Optional. Doesn't work
--
Nothing in life is ever easy - just get used to that fact.


"Per Jessen" wrote:

Use this line:

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

Regards,
Per

"KennyD" skrev i meddelelsen
...
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
.Range("C5").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it
inserts
it.
--
Nothing in life is ever easy - just get used to that fact.

.

Copy a Formula in a Macro
 

I changed it to the following and it worked.

..Range("C5").Select
ActiveCell.FormulaR1C1 =
WorksheetFunction.RoundDown((myCell.Offset(0, -1)), 0)

Thanks for the help.
--
Nothing in life is ever easy - just get used to that fact.


"KennyD" wrote:

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
.Range("C5").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it inserts
it.
--
Nothing in life is ever easy - just get used to that fact.