Excel 2010-VBA: Trying to create a filldown that is based on the current/active column

I have created a loop to create new columns until there is not next in the next columns. However I am trying to include a formula in this loop and I cannot get the drag down to be based on the active column. Right now i have:

ActiveCell.Select
Selection.Copy
Selection.AutoFill Destination:=Range("D5:D226")
Range("D5:D150").Select

I want the Range to be something like ("Activecell.Column:ActiveCell.Column"). I have also tried Range("5:150)" to capture only the rows I want my formula filled to, but that doesn't work. The goal is that I can use the loop to drag the formula down all columns with an active formula without having to alter every single column (which won't work because this will be used on data with different amounts of columns).

I have tried searching a bunch of forums but have not found anything like this.

Does this help?

Please Login or Register  to view this content.

I get the following error:
Run-time error '1004': You cannot change part of an array
Sorry, I forgot you cannot edit an array...is there a workaround for that?

Can you attach a sample sheet? That way we can see what's going on.

It works for me on the sample you sent, but I notice that your formula is not active (no = sign). Can you amend where the formula is active?

I'm not sure what you mean, there is an "=" before INDEX ?

Oh that is the formula I have for when I was writing the macro so I didn't copy and paste the code.

Dim x As Long
x = Cells(Rows.Count, ActiveCell.Column).End(3).Row
Selection.FormulaArray = _
"=INDEX(Data!R1C8:R1000000C8,SMALL(IF(R2C2=Data!R[-4]C3:R1000000C3,ROW(Data!R[-4]C3:R1000000C3)-MIN(ROW(Data!R1C3:R1000000C3))+1,""""),ROW(Data!R[-4]C3)))"
ActiveCell.Copy Range(Cells(2, ActiveCell.Column), Cells(x, ActiveCell.Column))

This is what my code looks like right now. Perhaps I have it in the wrong order? I currently have it set up in D5 but it should work the same as B5.

I have to run for now. It's my end of day. If no one else jumps in I will take another look tomorrow morning.

What are you selecting for the formula? Is it more than one cell? Again, I get an error on the formula itself, when I try to place it on the sheet. Can you provide a sample where it is already there?

Does this help?

I'm still getting the hang of VBA. Which part of the "x = ..." would I change?
I tried:

x = Cells(Rows.Count, ActiveCell.Column).End(3).150

x = Cells(Rows.Count, ActiveCell.Column).End(150).row

x = Cells(150, ActiveCell.Column).End(3).row

x = Cells(150.Count, ActiveCell.Column).End(3).row

x = Cells(Rows.150, ActiveCell.Column).End(3).row

I changed it for you in Post 13 for testing. You can't use Activecell.Column from your sample because it only goes too row 3, which is where the activecell is.

When I download the example spreadsheet and run the macro it works. However in my original spreadsheet it doesn't, it says can't alter array. Any idea why this might be?

I got the same error when I didn't change x. You need to indicate what the last row is in the macro. You can't use Activecell.Column because the row number is 3. Try using x = Activesheet.UsedRange.Rows.Count instead. Let me know how you come out.

Same error

Run this and let me know what x reads.

Please Login or Register  to view this content.

It came up as 225

It should work without error. I'm afraid I can't be of any more help. I'd have to see the original worksheet. If it worked on the sample it should work on the original.

Okay..thank you for your help!

Here is the original

Try:

Please Login or Register  to view this content.

Note: The change in the fill-down range. Activecell should be D5.

It works!! So it was selecting the wrong cell to pull down from because of that?

Yep. It was trying too change the activecell which had the formula. Therefore the reason for the error.

Thank you for the help and explanation!

You're welcome. Glad to help out and thanks for the feedback.