Forum Discussion
Use VBA to Autofill a Row until the end of the number of data in another row
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
83 Replies
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.
@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!!
' 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()))
