Hello,

I have a data set that contains formulas.
Due to the large size the formulas are only kept in the first row and
only copied down when report is updated monthly - these cells are then
hardcoded.

I cant copy formula down the whole way so need a loop to do copy
formula, calculate result and then hardcode before moving down to the
next row, each time taking the formula from row one.

Can anyone get me started on this?

thanks

Say your formulas are in cells D1 through H1. You want them copied in the
same columns in rows 2 through 50, and then replace the formulas with their
values. Copy this code and paste it in a standard module. Should get you
started. HTH, James

Sub Test()
Dim k As Long
For k = 2 To 50
Range("d1:h1").Copy _
Destination:=Cells(k, "d")
Range(Cells(k, "d"), Cells(k, "h")).Formula _
= Range(Cells(k, "d"), Cells(k, "h")).Value
Next k
End Sub

"ra" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I have a data set that contains formulas.
> Due to the large size the formulas are only kept in the first row and
> only copied down when report is updated monthly - these cells are then
> hardcoded.
>
> I cant copy formula down the whole way so need a loop to do copy
> formula, calculate result and then hardcode before moving down to the
> next row, each time taking the formula from row one.
>
> Can anyone get me started on this?
>
> thanks
>

If you can determine the extent of the rows by checking column 1 and the
formulas are in B1:M1 (as an example)

then this doesn't require any looping:

Sub copydown()
Dim lrow As Long
Dim r As Range, r1 As Range
lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B1:M1").Resize(lrow, 12)
Range("B1:M1").AutoFill r
Set r1 = r.Offset(1, 0).Resize(lrow - 1, 12)
r1.Copy
r1.PasteSpecial xlValues
End Sub

--
Regards,
Tom Ogilvy



"ra" wrote:

> Hello,
>
> I have a data set that contains formulas.
> Due to the large size the formulas are only kept in the first row and
> only copied down when report is updated monthly - these cells are then
> hardcoded.
>
> I cant copy formula down the whole way so need a loop to do copy
> formula, calculate result and then hardcode before moving down to the
> next row, each time taking the formula from row one.
>
> Can anyone get me started on this?
>
> thanks
>
>