Filldown stops in middle of the sheet

[Chris_Li]

Hi community,
again I need your help. I have columns with formulas and I would like to have these filled in automatically down to the last line.

My variable is set up like this:

Dim l As Long
l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

and in column P happens this:

Range("P1") = "BU Group 2" '>> Giving column name
Range("P2").Formula = "=VLOOKUP($N2,Centres!$B:$D,3,0)" '>> check for values in different tab
Range("P2:P" & l).Filldown '>> expected fill down of formular above

The filldown works prober until line 104.

But the report has a dynamic range constantly over 8000 lines


I checked the sheet the vlookup refers to, but can't find an error.

Is there something fron with my code or do I miss something else?

Please let me know if you need more information.

Cheers

Chris

 

[RoryA]

Your code is using column A of the active sheet to work out how far down to fill, so presumably it's either the wrong sheet, or column A is not populated for every row.

 

[Chris_Li]

Your code is using column A of the active sheet to work out how far down to fill, so presumably it's either the wrong sheet, or column A is not populated for every row.

Hi RoryA,

thanks for your quick answer and giving me new ideas. Now I have found out something new.
When I run the macro in the vba developer screen by clicking the run button , all is running as expected.

I have created a button and assigned the sub_form to it. Clicking the button create the error by stopping at row 104.
The macro assignment seems to be without an error as I can see the name of the Workbook followed by the Macro name and Macros in: is All Open Workbooks.

I have deletet the button from the sheet and created it new but the error stays.
I created a new button on a empty sheet, assiged the macro to it and it is not even going to row 104 but copying header into row 2.

Do you have any ideas for this issue?

Thanks again for your time

Cheers

Chris

 

[RoryA]

Is the button on the same sheet that the code is supposed to fill down on?

 

[Chris_Li]

Is the button on the same sheet that the code is supposed to fill down on?

Hi RoryA,
wow, you're quick.

No, the button is in a different tab. The user should be only in the Start Tab, clicking that button and by that, several tabs get worked of, by copying data from externel sources into the different tabs, performing serveral vlookups and formulars and fill them down. The sheet, where the user is in when he clicks the button, presents then the summary of all the tabs.

Thank you again

Chris

 

[RoryA]

Then you need to change activesheet in this line to the relevant sheet:

l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

 

[Chris_Li]

Then you need to change activesheet in this line to the relevant sheet:

l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Hi RoryA,

that fixed the issue! Thank you very much again for your quick answers and help.

Cheers

Chris

 

[RoryA]

Glad we could help.