Keelworm

Have an excel spread sheet, as follows


d_name | d_adress | d_date-in | d_date-out | formula:da-out - date-in |

Now have written a VBA form to input the d_* stuff, adding a new line to the
bottom of the spreadsheet each time. That's working fine, however, can't
for the life of me figure out how to copy the formulas down on to that row
as well, still retaining dynamic cell references (eg. d1-c1 ->becomes->
d2,c2 and so on as records go down)

This seems as though it should be a bloody easy process, but I'm stumped.

Suggestions please.

PG


--
*bikes@YOURTITSgoldingweb.co.uk*
take out YOUR TITS to get my attention
Then -> [C-50. c-90. DT-50. TY-175. kx80 CG-125, ER-5,]
Now -> [Bandit 600S - K&N, ART, HOSES]
*http://www.goldingweb.co.uk*

Andrewr At Work

"Keelworm" <bike@YOURTITSgoldingweb.co.uk> wrote in message
news:cb6ntq$phk$1@sparta.btinternet.com

> Have an excel spread sheet, as follows
>
>
> d_name | d_adress | d_date-in | d_date-out | formula:da-out - date-in |
>
> Now have written a VBA form to input the d_* stuff, adding a new line to the
> bottom of the spreadsheet each time. That's working fine, however, can't
> for the life of me figure out how to copy the formulas down on to that row
> as well, still retaining dynamic cell references (eg. d1-c1 ->becomes->
> d2,c2 and so on as records go down)

Instead of using cells(x,x).formula=cells(y,y).formula use;

cells(x,x).FormulaR1C1=cells(y,y).FormulaR1C1

That should sort it.

--
AndrewR, D.Bot (Celeritas)
Kawasaki ZX-6R J1
BOTAFOT#2,ITJWTFO#6,UKRMRM#1/13a,MCT#1,DFV#2,SKoGA#0 (and KotL)
BotToS#5,SBS#25,IbW#34, TEAR#3 (and KotL), DS#5, Keeper of the TFSTR#
The speccy Geordie twat.


--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Keelworm

Andrewr At Work wrote:

> "Keelworm" <bike@YOURTITSgoldingweb.co.uk> wrote in message
> news:cb6ntq$phk$1@sparta.btinternet.com
>
>> Have an excel spread sheet, as follows
>>
>>
>> d_name | d_adress | d_date-in | d_date-out | formula:da-out - date-in |
>>
>> Now have written a VBA form to input the d_* stuff, adding a new line to
>> the bottom of the spreadsheet each time. That's working fine, however,
>> can't for the life of me figure out how to copy the formulas down on to
>> that row as well, still retaining dynamic cell references (eg. d1-c1
>> ->becomes-> d2,c2 and so on as records go down)
>
> Instead of using cells(x,x).formula=cells(y,y).formula use;
>
> cells(x,x).FormulaR1C1=cells(y,y).FormulaR1C1
>
> That should sort it.
>

Cheers andrew. does the job.

PG
--
*bikes@YOURTITSgoldingweb.co.uk*
take out YOUR TITS to get my attention
Then -> [C-50. c-90. DT-50. TY-175. kx80 CG-125, ER-5,]
Now -> [Bandit 600S - K&N, ART, HOSES]
*http://www.goldingweb.co.uk*

Domènec

"Keelworm" <bike@YOURTITSgoldingweb.co.uk> escribió en el mensaje
news:cb6ntq$phk$1@sparta.btinternet.com...

> Now have written a VBA form to input the d_* stuff, adding a new line to
the
> bottom of the spreadsheet each time. That's working fine, however, can't
> for the life of me figure out how to copy the formulas down on to that row
> as well, still retaining dynamic cell references (eg. d1-c1 ->becomes->
> d2,c2 and so on as records go down)

Try start recording a macro, do whatever, stop the recording and then have a
look at the generated code.

Keelworm

Domènec wrote:

> "Keelworm" <bike@YOURTITSgoldingweb.co.uk> escribió en el mensaje
> news:cb6ntq$phk$1@sparta.btinternet.com...
>
>> Now have written a VBA form to input the d_* stuff, adding a new line to
> the
>> bottom of the spreadsheet each time. That's working fine, however, can't
>> for the life of me figure out how to copy the formulas down on to that
>> row as well, still retaining dynamic cell references (eg. d1-c1
>> ->becomes-> d2,c2 and so on as records go down)
>
> Try start recording a macro, do whatever, stop the recording and then have
> a look at the generated code.

not exactly what I had in mind.

PG

accepted answer: thanks go to Andrew@work
--
*bikes@YOURTITSgoldingweb.co.uk*
take out YOUR TITS to get my attention
Then -> [C-50. c-90. DT-50. TY-175. kx80 CG-125, ER-5,]
Now -> [Bandit 600S - K&N, ART, HOSES]
*http://www.goldingweb.co.uk*