Auto filling using macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that I need to reformat each month in order for it to be
imported into another application. The spreadsheet varies in length each
month, from say 100 lines one month to 600 lines the following month.

I have recorded a macro that deletes columns, creates formulae etc.
I wish to 'auto fill' the formulae I create down the entire length of a
column. However, the fact that the spreadsheet has a different number of
lines each month means that performing the autofill via a simple macro does
not work.

I know there is a bit of code that will allow me to find the last line in
the spreadsheet and autofill down to that line but I can't remember how to do
it. Any suggestions?

Thanks in advance
 
Here is an example code:

Range("B2").Formula = "=LOOKUP(A2,'Employee Numbers'!C:D)"
If Range("A3") > "" Then
Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown
End If

This uses the last row of column A [Range("A2").End(xlDown))] as where to
fill down the formula in column B from B2.

Mike F
 
Thanks!

Mike Fogleman said:
Here is an example code:

Range("B2").Formula = "=LOOKUP(A2,'Employee Numbers'!C:D)"
If Range("A3") > "" Then
Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown
End If

This uses the last row of column A [Range("A2").End(xlDown))] as where to
fill down the formula in column B from B2.

Mike F
bernard said:
I have a spreadsheet that I need to reformat each month in order for it to
be
imported into another application. The spreadsheet varies in length each
month, from say 100 lines one month to 600 lines the following month.

I have recorded a macro that deletes columns, creates formulae etc.
I wish to 'auto fill' the formulae I create down the entire length of a
column. However, the fact that the spreadsheet has a different number of
lines each month means that performing the autofill via a simple macro
does
not work.

I know there is a bit of code that will allow me to find the last line in
the spreadsheet and autofill down to that line but I can't remember how to
do
it. Any suggestions?

Thanks in advance
Click to expand...
Click to expand...
 
I have a question about this Macro
My understanding is it looks at column A to determine how far to fill Column
B starting with cell B2.
What exactly does this portion

"=LOOKUP(A2,'Employee Numbers'!C:D)"

Have to do with it...I am getting some sort of error when I attempt to use
this macro in my spreadsheet

Mike Fogleman said:
Here is an example code:

Range("B2").Formula = "=LOOKUP(A2,'Employee Numbers'!C:D)"
If Range("A3") > "" Then
Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown
End If

This uses the last row of column A [Range("A2").End(xlDown))] as where to
fill down the formula in column B from B2.

Mike F
bernard said:
I have a spreadsheet that I need to reformat each month in order for it to
be
imported into another application. The spreadsheet varies in length each
month, from say 100 lines one month to 600 lines the following month.

I have recorded a macro that deletes columns, creates formulae etc.
I wish to 'auto fill' the formulae I create down the entire length of a
column. However, the fact that the spreadsheet has a different number of
lines each month means that performing the autofill via a simple macro
does
not work.

I know there is a bit of code that will allow me to find the last line in
the spreadsheet and autofill down to that line but I can't remember how to
do
it. Any suggestions?

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top