using formulas in macros
 

I am using a macro to carry out a repeated task which includes copying a
selection of data into a separate worksheet . I then want to create a
formula with the copied data to create further info in another column.
However, how can I tell excel to copy the formula into all the rows which
hold the records, but only for those rows that do hold records. The number of
records copied into the worksheet can vary each time and I only want the
formula replicated in the rows of copied data. Hope this makes sense. Can
this be done and how do I do this?

using formulas in macros
 

calculate the number of rows

NumRows = Cells(rows.Count,"A").End(xlUp).Row

and the autofill the formula, assuming you loaded it into A1

Range("A1").Autofill Range("A1").Resize(NumRows )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BBEXCELNOVICE" wrote in message
...
I am using a macro to carry out a repeated task which includes copying a
selection of data into a separate worksheet . I then want to create a
formula with the copied data to create further info in another column.
However, how can I tell excel to copy the formula into all the rows which
hold the records, but only for those rows that do hold records. The number
of
records copied into the worksheet can vary each time and I only want the
formula replicated in the rows of copied data. Hope this makes sense. Can
this be done and how do I do this?

using formulas in macros
 

It is much easier to copy the formula down the whole column and then go back
and clear out the cells in which you don't want the formula.
--
Gary's Student


"BBEXCELNOVICE" wrote:

I am using a macro to carry out a repeated task which includes copying a
selection of data into a separate worksheet . I then want to create a
formula with the copied data to create further info in another column.
However, how can I tell excel to copy the formula into all the rows which
hold the records, but only for those rows that do hold records. The number of
records copied into the worksheet can vary each time and I only want the
formula replicated in the rows of copied data. Hope this makes sense. Can
this be done and how do I do this?

using formulas in macros
 

Thank you very much for your response.
I perhaps did not make myself clear - I am not actually writing the macro
myself but using the excel macro record facility. How would I or where would
I insert Numrows? Sorry to seem rather thick!! Also would you be able to tell
me how to delete a number of blank rows in one of the worksheets also. The
blanks being above a list of data?

"Bob Phillips" wrote:

calculate the number of rows

NumRows = Cells(rows.Count,"A").End(xlUp).Row

and the autofill the formula, assuming you loaded it into A1

Range("A1").Autofill Range("A1").Resize(NumRows )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BBEXCELNOVICE" wrote in message
...
I am using a macro to carry out a repeated task which includes copying a
selection of data into a separate worksheet . I then want to create a
formula with the copied data to create further info in another column.
However, how can I tell excel to copy the formula into all the rows which
hold the records, but only for those rows that do hold records. The number
of
records copied into the worksheet can vary each time and I only want the
formula replicated in the rows of copied data. Hope this makes sense. Can
this be done and how do I do this?

using formulas in macros
 

I would need to see your recorded code to tell you, I can't read minds.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BBEXCELNOVICE" wrote in message
...
Thank you very much for your response.
I perhaps did not make myself clear - I am not actually writing the macro
myself but using the excel macro record facility. How would I or where
would
I insert Numrows? Sorry to seem rather thick!! Also would you be able to
tell
me how to delete a number of blank rows in one of the worksheets also. The
blanks being above a list of data?

"Bob Phillips" wrote:

calculate the number of rows

NumRows = Cells(rows.Count,"A").End(xlUp).Row

and the autofill the formula, assuming you loaded it into A1

Range("A1").Autofill Range("A1").Resize(NumRows )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BBEXCELNOVICE" wrote in
message
...
I am using a macro to carry out a repeated task which includes copying
a
selection of data into a separate worksheet . I then want to create
a
formula with the copied data to create further info in another column.
However, how can I tell excel to copy the formula into all the rows
which
hold the records, but only for those rows that do hold records. The
number
of
records copied into the worksheet can vary each time and I only want
the
formula replicated in the rows of copied data. Hope this makes sense.
Can
this be done and how do I do this?