Filldown stops in middle of the sheet

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the button on the same sheet that the code is supposed to fill down on?
 

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then you need to change activesheet in this line to the relevant sheet:

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

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Then you need to change activesheet in this line to the relevant sheet:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad we could help. :)
 

Forum statistics

Threads
1,175,528
Messages
5,897,947
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top