Macros and Autofills problem

P

Paul Adams

Can anybody help with this problem I'm having on Excel?

I'm trying to write a Macro on Excel that gets information from
database and the filters the information by various calculations an
lookups.

I get stuck because information I retrieve from the database does no
always have the same amount of rows.

So if I record a Macro, retrieve the date from the database, write
formula in the first row and then double click on the lower right han
box to copy the formula down to the other rows of information, it work
fine.

But if I run the Macro again and there are more rows from the database
the formula is only copied down to the number of rows there where whe
I recorded the Macro and so does not achieve what I want.

I have tried editing the Macro but the best I can do is get it t
autofill the whole column but then I end up with lots of #N/A's at th
bottom which does not help plus it slows the Macro down when I run it


This must be a straightforward thing to sort it out but I can't fin
it, if the Macro just did as I recorded, it would work fine
 
D

Debra Dalgleish

You can find the last filled row in an adjacent column, and fill down to
that:

'====================
Dim r As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
r = ws.Cells(Rows.Count, 2).End(xlUp).Row

ws.Cells(2, 3).FormulaR1C1 = "=RC[-1]*2"
ws.Range("C2").AutoFill _
Destination:=ws.Range(Cells(2, 3), Cells(r, 3))
'=======================
 
P

Paul Adams

Sorry, I'm not very good at all with visual basics. I tried pasting i
in to a macro but could not get it to work. Sounds like just the job i
I could though. How would I add it into my macro.

Thanks

Pau
 
D

Debra Dalgleish

If I record the steps while entering a formula, and autofilling, the
code looks like this:

'=======================
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*2"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C13")
Range("C2:C13").Select
'=========================

You want to make the last row in the destination a variable, based on
the existing number of rows in another column.
So, at the top of the macro, just below the Sub line, define the variables:

Dim r As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
r = ws.Cells(Rows.Count, 2).End(xlUp).Row

Change the sheet name to the one in your workbook.
Change the 2 in the r = line to the column in which you want to count
the rows. For example, to count rows in column F, use 6 instead of 2.

Then, change the Destination. To simply replace the C13 in the above
code, I could use:

Selection.AutoFill Destination:=Range("C2:C" & r)
 
P

Paul Adams

Thanks Debra, using your instructions I got it to work in a dummy macr
i did and it worked fine. I'll put it into the proper macro and give i
a try.

Thanks for your time and help

Pau
 
Top