Dec 14 2017
06:32 AM
- last edited on
Jul 25 2018
09:46 AM
by
TechCommunityAP
Dec 14 2017
06:32 AM
- last edited on
Jul 25 2018
09:46 AM
by
TechCommunityAP
Hello,
I need some help with the following problem:
The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to.
The situation looks like this:
The code that I have so far looks like this:
Sheets(3).Select
Range("AY2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select
The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662.
I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E.
Thank you very much in advance,
Kai
May 18 2020 07:46 AM
May 19 2020 03:41 AM
May 23 2020 05:27 AM
Hi,
Please use this below instead, and tell me what you think.
Sub AutoFillToRight()
Dim lastCellFromRight As String
ActiveCell.Select
lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address
If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub
Range(ActiveCell.Address, lastCellFromRight).FillUp
Range(lastCellFromRight).Select
End Sub
Regards
Oct 06 2020 11:42 AM
Nov 03 2020 02:37 AM
@W4rcloud Buggy code. The xldown trick does not work in your example because all cells below selection are empty. Please test your code before posting. Thanks.
' version 1
Sub AutoFill_lengthy_self_explanatory()
Dim startcell, endcell As Range
Set startcell = Selection.Range("A1")
Set endcell = startcell.Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count())
Selection.AutoFill Destination:=Range(startcell, endcell)
End Sub
' version 2: same but one-liner that you can use instead of a function:
Selection.AutoFill Destination:=Range(Selection.Range("A1"), Selection.Range("A1").Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count()))
Nov 03 2020 02:38 AM
' version 1
Sub AutoFill_lengthy_self_explanatory()
Dim startcell, endcell As Range
Set startcell = Selection.Range("A1")
Set endcell = startcell.Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count())
Selection.AutoFill Destination:=Range(startcell, endcell)
End Sub
' version 2: same but one-liner that you can use instead of a function:
Selection.AutoFill Destination:=Range(Selection.Range("A1"), Selection.Range("A1").Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count()))
Jan 04 2021 08:31 PM
Feb 03 2021 08:29 AM
Worked perfect! Thanks!@Haytham Amairah
Feb 23 2021 11:24 AM
@Haytham
I apologize that I am not able to find what I need among so much work toward this subject...please help.
I have a workbook that has 4 sheets, all tables. I need to be able to enter a new line of information in the first table (a non-formulated table) and then have all the other tables drag down formulas to match the number of data rows in the first table... How do I get this to do this automatically? What would be the code on the button to get this to happen? Any help would be appreciated!!
Feb 23 2021 01:41 PM
Jun 25 2021 03:51 AM
Hi Just wanted to know whether we can have the variable for the first row in AY2. So instead of 2 can we add some variable at the beginning & replace that variable at the code as my first row is not fixed.
Jul 01 2021 04:59 PM
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.
Jul 17 2021 10:32 PM
Aug 28 2021 09:22 PM
How do you lookup then copy and paste data when there's previous data from the above cell. My file is being updated daily so there's new data. I need to make a VBA that looks up the new data then copy and paste it as values.
1. I need to look up the last entry on Column C on Sheet1 then enter the data on Column B (IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!B:B,Sheet2!A:A,0)),"") on Sheet2
2. Autofill data until the last row
3. Copy and paste as values
4. Run again once there's new data on
I don't know what code to use on the autofill part.
Sep 13 2021 08:48 PM
Hi
I tried the same for simple macro but it didn't work.
Can pls you help me
Oct 15 2021 12:38 AM
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.
Sep 11 2022 11:11 AM
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
Sep 26 2022 02:30 AM
Jan 30 2023 01:49 AM
Mar 16 2023 03:34 AM
@Haytham Amairah hi I ran a macro to sort a file, the last step is to sort data by "sales unit this year "means data from Column H must Goto Column G. to do this i used sort function to move all the data to column G now to move the remaining data from H to G I went to the last cell of G that has data , move one cell below it to the empty cell the use a formula to bring data from H(e.g lastE
mptycellOF G=H56) and tried to autofill this to the equivalent last H cell that has data
this is the code that i added on top of the quote that was recorded by VBA when i was recoding a macro
Range("A4").Select
Selection.End(xlToRight).Select
Range("G4").Select
Selection.End(xlDown).Select
Range("G1059044").Select 'go all the way down to cell 1059044
Selection.End(xlUp).Select 'then co the way up until you find data
lastCell = ActiveCell.Address(True, False) 'trying to give the lastcell the address of the active cell because last cell always changes according to the length of the data
ActiveCell.Offset(1, 0).Select 'from the active cell which is supposed to be the last cell go one cell down
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[1]"
Selection.AutoFill Destination:=Range("lastCell" & Range("G4" & Rows.Count).End(xlUp).Row) ' want it to select the dynamic cell and autofill the formula
Range(Selection, Selection.End(xlDown)).Select