Forum Discussion
Use VBA to Autofill a Row until the end of the number of data in another row
Hi Haytham Amairah,
I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from:
Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Selectto:
Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Replace these lines of code:
Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select
With this:
Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Give it a try!
And provide me with any feedback!
Hey Haytham Amairah
I know this is an old post, but i hope that you have an solution for my problem
I want to make a new line for this one wich works fineSelection.AutoFill Destination:=Range("C2:C11"), Type:=xlFillFormats
Range("C2:C11").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
---------------------------------------------
But when i use this because of i also want to autofill a row until the end of the number of data in another row:Selection.AutoFill Destination:=Range("C2:C" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Al the info in line B and C is the same as in B2 and C2
I hope you can help me.
Thanks for reading thisThank You Sooooo MUCH!! :)
I created an account just to thank you, it worked perfectly !
- i have two sheets where if the heading of a column matches with the value in another sheet q column then vlook up is executed. if it is false the it will jump to next heading this sequence goes on for 8 times .
but in third row though it is matching with the heading the we lookup formula is identifing a273 as 103642 where as it is 066-50007-1531
=IFERROR(IF(ac_matched!$B$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$J:$J,1,0),IF(ac_matched!$C$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$K:$K,1,0),IF(ac_matched!$D$1=removals_only!Q273,VLOOKUP(ac_matched!A273,ac_matched!$L:$L,1,0),IF(ac_matched!$E$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$M:$M,1,0),IF(ac_matched!$F$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$N:$N,1,0),IF(ac_matched!$G$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$O:$O,1,0),IF(ac_matched!$H$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$P:$P,1,0),IF(ac_matched!$I$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$Q:$Q,1,0),"")))))))),"nhpn")
i have this problem with vlook up hope you have solution to it. - @haytham I somewhat have the same issue however am doing index-match instead of vlookup. Solution is the same hence am very grateful that I stubble upon this tread
I want to do a similar thing but I want to copy the formula in cell AY2 down for the length of the column E, not autofill.
Thanks
Hi, thank you for sharing your wisdom.
It worked for a while until lately i found out it occurred error if there is only one row,
and i dont know how to correct it.
Would you please me how?
Thank you.
Hi
I tried the same for simple macro but it didn't work.
Can pls you help me
- What's for the "E" ?
Hello Haytham,
I am have a similar issue. I need the formula to fill if the columns and rows have data. Due to the rows and columns fluctuating periodically and daily. The formula will be in D7.
Thank you for your time.
