Autofill multiple formulas in columns down to the last row with data in it in column B - ignores column B entries

[armchairandy]

Hi

I have different formulas in 1106 columns relating to the 1st row (Column B)
There are up to 500 rows where data can be put in. This can lead to a lot of formulas, as I have up to 20 sheets of this.
The 500 rows are not always used, and I do not want unused formulas in the sheets
this is an automatic worksheet used by different users & so I need to automate the results, and not just tell people to darg to the bottom.

So...........

In column B, if there are 10 entries B17 to B26 - I need the formulas autofilled along J17 to AQU17 down to Row 26

I used the below formula, but when I ran it, the macro autofilled past row 26

Range("J17").Select
Range("J17:AQU17").AutoFill Destination:=Range("J17:AQU" & Worksheets("Test").Cells(Rows.Count, "B").End(xlUp).Row)

How do I make it stop at row 26??

Many thanks in advance

 

[MARK858]

Why are most of the ranges referring to the active sheet but the last row is being calculated based on column B in a sheet called Test? you don't mention a sheet called Test in your question so is this correct as by your description it should be using the activesheet?

 

[armchairandy]

Why are most of the ranges referring to the active sheet but the last row is being calculated based on column B in a sheet called Test? you don't mention a sheet called Test in your question so is this correct as by your description it should be using the activesheet?

Mark

all the ranges are in Test. My knowledge in vba is limited and I did not know how to change the macro for data in the same worksheet. The code I took this from used a different sheet for the rows to count for the autofill.

Hope this makes sense?

Andrew

 

[MARK858]

If all the ranges are in the sheet called Test and that sheet is active then the code should work.
What does

Sub TestIt()
MsgBox Worksheets("Test").Cells(Rows.Count, "B").End(xlUp).Row
End Sub

give you?
If the answer isn't 26 what is in Column B? is it formulas where sometimes the result is "" ? if yes are the cells returning "" past row 26?

 

[armchairandy]

the answer was 520

the data in column B is text that is pasted in each time and can vary from 10 to 500 lines, but to date I have not pasted to 520 - and after each use, the data in column b is deleted

Andrew

 

[MARK858]

In a blank cell put the formula

=CODE(B520)

What does the formula return?

 

[armchairandy]

I put it in B68 & it returned 67

Andrew

 

[MARK858]

That is an unexpected answer as Ascii character 67 is a capital C. Click on cell B520 and tell me what appears in the formula bar.

 

[armchairandy]

Apologies

there was text in B520, I deleted it and where the code is B68 now says #Value!

Regards

 

[MARK858]

What does the code in post #4 now give you (after clearing the formula in B68)?