Auto copy formula from a cell, to other cells when adjacent cell on row contains data?

[helpneeded2]

I am trying to create a spreadsheet that will be used by multiple people in which data from a an SQL pull will be pasted into the sheet. This data may vary from 100 rows of data to 20,000 rows.

For best sheet performance I would like to avoid having a formula in 20,000 rows that tests whether there is data in each of the rows, and outputs a value if there is data. My sheet has several similar data tabs, and when I placed in the formula down the 20,000 rows on each tab, the sheet size increased 10-fold and the time to calculate is now measured in minutes.

Although I have had very minimal exposure to VBA, I would like to have a script that would copy a formula from the first row (adjacent to existing data), and paste it down the column for every other adjacent row with data.

To help simplify what I am trying to do, here is an example:

	A	B	C	D
1	22	23	=A1+B1
2	44	23
3	2343	339
4

In the above example, I want any formula in C1 to be copied down to the last row in Column C in which data exists in Column A.

While looking at examples from a browser search, I think the most accurate VBA I found was this:

Sub test()
Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1").AutoFill Destination:=Range("C2:C" & lastrow), Type:=xlFillDefault
End Sub

However when I try to run this I get an error. (Run-time error '1004': AutoFill method of Range class failed)

Could someone please post a suggestion on how I could do this.

Thank you.

 

[helpneeded2]

It looks like I can answer my own post. I was playing around with some testing and I found that if I change the Destination Range from "C2:C" to "C1:C", that it now works.

However if there is a better way I should be doing this, I certainly would be interested in knowing to help me educate myself on how I can use VBA.

 

[Fluff]

Not necessarily a "better" way, just another option

Sub test()
    Range("C1:C" & Range("A" & Rows.count).End(xlUp).Row).FillDown
End Sub

 

[helpneeded2]

Also, I will be needing to copy about 12 formulas down, and since the AutoFill method only works in 1 direction, should I instead be using a copy function, rather than an autofill function to copy the formulas?

 

[Fluff]

What columns are the formulae in?

 

[helpneeded2]

What columns are the formulae in?

The columns will all be adjacent to each other, so ideally a range copy would work. On my actual sheet the formulas are in columns BR to DS.

 

[Fluff]

In that case how about

Sub test()
    Range("BR1:DS" & Range("A" & Rows.count).End(xlUp).Row).FillDown
End Sub

 

[helpneeded2]

In that case how about

Sub test()
    Range("BR1:DS" & Range("A" & Rows.count).End(xlUp).Row).FillDown
End Sub

Works perfectly! Thanks a lot. I've been trying to review and understand other examples of this from my browser search and they all are quite different and involves multiple lines of code. That's a real nice 1-line of code that does exactly what I need. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[cat2ebay]

You're welcome & thanks for the feedback.

Hello,

Is there a way to do this such that the macro looks at the cell directly to the left (or right) and copies/pastes down as long as the adjacent cell has a value or formula?

Using the example above, if the cursor was in C2, what would be the syntax for a relative query to look at B2 and copy/paste C1 into C2 if B2 is populated?

Not sure if this was clear, but trying to understand how to write the macro to work wherever the cursor might be in any worksheet.

Thanks