Need help with VBA for Selection.Autofill past break in data in preceding column

Hi EE!

I'm working on a VBA macro for Excel and I am having a problem with Selection.Autofill

What is happening (I think...) is that the formulas are only autofilled until there is a break in the data from the preceding column.  My formulas, beginning in column U, are not dragged down past the place where column T has two blank cells.  

I've tried working around this by doing a second autofill, but my results were unreliable since the data changes from each instance.  Is there a way to continue the autofill past the break in column T, while still stopping at the end of data in column A?

Here's the portion I'm referring to.  If you would like to see this in context of the larger VB I can post that as well.  

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
    Selection.AutoFill Destination:=Range("U2:BF" & LR), Type:=xlFillDefault
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you post your sample file..Along with what formula you are trying to apply as their is a better way to do this without autofill..but in order to do that need further details which is what formula you are applying and what range you want to apply to..

A sample workbook always helps answering these questions..
y9906Author Commented:
Sorry friend, it has some sensitive work stuff and I don't know that I could sanitize it because I have it set to pull and populate new data from a server.  

What was the better way you had in mind?  Basically, I just need to insert formulas across a range of perhaps a dozen columns and several thousand rows.  Right now, I'm trying to autofill formulas from a dummy row at the top, but if there's an easier way I'd love to read up on it.
Saurabh Singh TeotiaCommented:
Can you post a replica as in sample worksheet with sample data..Like i said you can apply formula in 1 go rather then doing autofill which i can tell you once i see the data..
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

y9906Author Commented:
Can you describe the general logic behind how you apply the formulae in one go instead of using Autofill?
Saurabh Singh TeotiaCommented:
You can use something like this...Assuming you want to apply formula in B2 linking them to a2 till the last row it can be wrote like this...

dim lrow as long

range("B2:B" & lrow).formula = "=TRIM(A2)"

Open in new window

The above code will apply formula from row-2 till the last row of A Column automatically in B Column...

y9906Author Commented:
Close, but no cigar...

I was able to take your suggestion and implement it to my sheet, but the problem is that my formula reference cells in their own row, and I want the rest of the formula to reference cells relative to their location.  The references need to increment as they go down the sheet.  

For example, Let's say that the formula in U2 is =T2

So, when I use "=TRIM(U2)" in cell U3, the formula is still =T2 when I want it to be =T3.  

I feel like I'm very close otherwise, though.
Saurabh Singh TeotiaCommented:
If you are driving it correctly it should automatically update.. What i mean by that lets say ..

In U2 and U3 you apply..

Range("U2:U3").FORMULA = "=TRIM(T2)"

In U3 you will find it updated to U3 Automatically till the time you don't put dollar sign or you say trim(t1)

y9906Author Commented:
Hmm, not seeing that.  

I'm using Range("U3:BF" & LR).Formula = "=trim(U2)"

This means that the formula in U3 is now =TRIM(U2), U4 IS now =TRIM(U3), etc.  What seems to happen is that the cell value for U3 is the same as the cell value for U2.  This is incorrect, since the formula in cell U3 is =T3, not =T2.  

The actual formula in U2 is =VLOOKUP(G2,'Normalized Employee ID'!A:E,5,FALSE).  I want the formula in U3 to be =VLOOKUP(G3,'Normalized Employee ID'!A:E,5,FALSE).
Saurabh Singh TeotiaCommented:
If you simply apply this formula which is...

Range("U2:U3").Formula = "=VLOOKUP(G2,'Normalized Employee ID'!A:E,5,FALSE)"

You will see in U3 it's equal to G3..

Now the formula you are applying at the first place the trim one you doing it incorrect remember i said the row number should be same which is not in your case you are applying formula in row-3 and giving reference in formula of row-2 which is creating a problem if you give the reference of row-3 in the formula rather then row-2 since your starting point is row-3 you will see updated results..Just try the formula which i gave to you assuming you are starting at row-2 so in both the places please notice i have used row-2 only as a reference point...
y9906Author Commented:
I think I understand now.  

I thought your original instruction was that I reference the formulas in row 2 from row 3, my mistake there.  

I have 38 columns with formulas that I was originally trying to autofill, so this might take a little while to build out all of them.  

I tried it with just Column U to test:    

   LR = Cells(Cells.Rows.Count, "A").End(xlUp).Row
   Range("U2:U" & LR).Formula = "=VLOOKUP(G2,'Normalized Employee ID'!A:E,5,FALSE)"

This worked up until Row 17099.  For some reason, the formula is not applied to Row 17100 and below, even though data exists in Column A until Row 20802.  

Any thoughts?  Appreciate all the help so far.  I've definitely learned a trick or two :)
Saurabh Singh TeotiaCommented:
Can you change this line...

   LR = Cells(Cells.Rows.Count, "A").End(xlUp).Row

To this...

 lr = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

This will find the last row irrespective of column reference and will do what you are looking for...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
y9906Author Commented:
Very cool!
I had to run it twice for it to work, but it looks like that will work for almost every column.  

I did run in to an issue with columns for which the formulas contain quotation marks.  
For example:  
Range("ab2:ab" & LR).Formula = "=IF(A2="","","WRK")"

Is there a better way to write this, so all those quotation marks don't confuse the compiler?
Saurabh Singh TeotiaCommented:
Use this...

Range("ab2:ab" & LR).Formula = "=IF(A2="""","""",""WRK"")"
y9906Author Commented:
Ah, I see.  The answer to too many quotation marks is just more quotation marks :)

Thanks a lot, Saurabh.  I THINK I can do the rest from here.  Appreciate the diligence!
Saurabh Singh TeotiaCommented:
Yw..Happy to Help.. :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.