copying formulas in vba

[Michael]

Hey guys.

I was wondering if someone could help me. I am writing a vba script that
takes in data, analyzes it, and then copies the results to a new file. I am
having a problem with two things.

1) I am using a template for the new file so there are a lot of formulas
(sums and std) already defined and ready to use. However, there are some
instances where there is a random amount of additional data I have to put in.
So, I have to apply the same formulas to this new data. How do I copy
formulas from one cell to another (allowing for a change in row) in vba?

Lets say cell(1,4) has the formula "=sum(A1:C1)
If I do: cells(4,4).formula = cells(1,4).formula
then cell(4,4) has the formula "=sum(A1:C1)"

How do I make it become "=sum(A4:C4)" using vba?

2) For these new cells, I also have to format some columns (currency, date,
etc) them and also put borders around them. How do I set these properties in
vba?

I greatly appreciate and assistance you could provide.

Thanks,

-Michael

[Bob Phillips]

1)

Cells(1, 4).Copy
Cells(4, 4).PasteSpecial Paste:=xlPasteFormulas

2) There is a BorderARound property for a range that you could use. Check it
out in help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <[email protected]> wrote in message
news:[email protected]...
> Hey guys.
>
> I was wondering if someone could help me. I am writing a vba script that
> takes in data, analyzes it, and then copies the results to a new file. I
am
> having a problem with two things.
>
> 1) I am using a template for the new file so there are a lot of formulas
> (sums and std) already defined and ready to use. However, there are some
> instances where there is a random amount of additional data I have to put
in.
> So, I have to apply the same formulas to this new data. How do I copy
> formulas from one cell to another (allowing for a change in row) in vba?
>
> Lets say cell(1,4) has the formula "=sum(A1:C1)
> If I do: cells(4,4).formula = cells(1,4).formula
> then cell(4,4) has the formula "=sum(A1:C1)"
>
> How do I make it become "=sum(A4:C4)" using vba?
>
> 2) For these new cells, I also have to format some columns (currency,
date,
> etc) them and also put borders around them. How do I set these properties
in
> vba?
>
> I greatly appreciate and assistance you could provide.
>
> Thanks,
>
> -Michael