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
Aug 16 2019 05:07 AM
Thanks for your quick reply!
I have an Excel file in which I import various reports and in this file I have a sheet, where I use those data for my formulas.
The various reports I import have the same length but differ in length every day.
In my main sheet where I use the imported data the first row always stays the same with all kind of formulas. However, I want my Macro to autofill the other rows until there is no more data in one of the imported reports.
Right now I have to set the autofill range manually. So if the report is too long, it's either cut or if it's too short I get errors.
Hope that was understandable.
Kind regards
Aug 16 2019 07:32 AM
It seems that this situation is different!
But I think you need to somehow get the total number of rows in the imported reports and then use that number in the macro.
If you can attach your file or a sample of it, this is would be helpful!
Aug 16 2019 08:39 AM
Thank you for helping me out with this.
I removed all data and a settings sheet but the important data is still there.
The macro-button is in the sheet "Output" Cell "F1"
The length should be the same as I got days in the reports. The totals column excluded.
Aug 16 2019 10:11 AM
Hi,
I've updated the code behind the Load button to fill down the rows based on the number of rows in (yield) sheet.
This is the code:
Sub FillRowsDown()
'Get the last row in sheet (yield) and save it in the lastRow variable
Dim lastRow As Long
lastRow = Sheets("yield").Range("A" & Rows.Count).End(xlUp).Row
'Subtract the first 6 rows from the lastRow
lastRow = lastRow - 6
'Subtract the Total row (if exists)
If LCase(Sheets("yield").Range("A" & Rows.Count).End(xlUp).Value) = "total" Then
lastRow = lastRow - 1
End If
With Sheets("output")
.Range("A4:U4").AutoFill Destination:=Range("A4:U" & lastRow + 3)
.Range("D4").Select
End With
End Sub
Hope that helps
Aug 16 2019 10:20 AM - edited Aug 16 2019 10:24 AM
Aug 23 2019 02:59 AM - edited Aug 23 2019 05:23 AM
Hi Haytham,
If I use to populate Row H to length of Row E
Sub Test()
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub
What would I need to add/change to get content from single Cell A2 and fill down from next empty in H
(the content of A2 changes and would like to see all changes updated in H rather than just overwriting)
Aug 23 2019 05:43 AM
Hi,
If I understand you well, nothing has to change in the code!
You need to use the below formula in cell H2 to link this cell to cell A2.
=A2
After then, run the code so that you fill this formula down until the last row in column E and make the column H and A linked together.
Regards
Aug 24 2019 01:09 AM
Thanks for your help Haytham,
unfortunately I am probably doing something wrong as when I link the Cells then run macro rather than
filling down with the text that is in A2 - cells in H fill down with a zero
Aug 24 2019 07:09 AM
Aug 25 2019 09:55 AM - edited Aug 25 2019 11:27 AM
Aug 25 2019 11:03 AM
If you need to fill the column H with the content of only cell A2 so that each cell in column H will be filled with the same text in cell A2.
Then you just need to lock the formula in cell H2 as follows:
=$A$2
Aug 29 2019 01:10 PM
Hi Amairah,
I have a question somewhat similar to the post above.
Currently, I want to create a column with a function and want to autofill the column with the function until the end of the row
However, I wish to put in the number to rows so that it works on any worksheet (maybe with different number of rows)
Screenshot below is the formula I want to put into the column and autofill until the end of the row for any worksheet.
Also, this is what I have so far
Sub MacroCode_Column fill
Dim myFirstColumn As Integer
Dim myLastColumn As Integer
Dim myFirstFieldRow As Integer
Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer
myFirstFieldRow = 5
myFirstDataColumn = Cells(5, Columns.Count).End(xlToLeft).Column + 1
myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
'Multiple Occurred
ActiveSheet.Cells(myFirstFieldRow, myFirstDataColumn + 6).Select
ActiveCell.Formula = "MCO Incurred"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"
Selection.AutoFill Destination:=Range(Cells(myFirstFieldRow + 1, myFirstDataColumn + 6), Cells(myLastDataRow, myFirstDataColumn + 6))
Thank you in advance!!
Aug 30 2019 08:16 AM
Hi,
Based on what you want to fill the formula down?
You see that the solutions here in this conversation focus on filling the rows in a column based on the number of rows of another column.
Aug 30 2019 08:31 AM
Hi Amairah,
Sorry if I asked a non-related question to the post but regarding to your question - basically I want to update the number of rows to the formula below using the following variables (which I created to set first row to be 5 and the last row to be determined based on data I'm using)
Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer
myFirstFieldRow = 5
myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
and the formula I want to update is below
ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"
Currently, the rows in the formula is in numbers but I wish to replace it using dimensions.
I would greatly appreciate it if you could help or direct me to where I can ask.
Thanks!!
Sep 16 2019 10:57 AM - edited Sep 16 2019 11:00 AM
Oct 05 2019 08:58 AM
I tried your suggestion and it works fine, but not for cases where there is only 1 row of data or no data (only heading). I have a heading in row 1, formula in row 2 (cell E2) which I'm trying to copy to the end of the table using this code:
Selection.AutoFill Destination:=Range("E2:E" & Range("C" & Rows.Count).End(xlUp).Row)
Many thanks in advance for your advice.
Sarka
Oct 10 2019 09:56 PM
Hi,
Sorry for the late reply!
Please attach a sample of the data you're working to fit the code on it.
Regards
Nov 23 2019 11:12 PM
Hi Haitham
I am new to VBA and I am trying to amend the ActiveCell.End(xlDown) part so that the formula (in column A) stops at the last row that contains data in column A. and again starts to autofill from this column to the next contains data and goes on,
and thank you in advance.
this is a screenshot:
Sub autofill()
Dim Wad As String
Wad = Cells(Rows.Count, "A").End(xlUp).Row
Range("A30").Select
Selection.autofill Destination:=Range("A30:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select
Selection.autofill Destination:=Range("wad:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select
End Sub
Waddah
Nov 26 2019 08:56 AM
Hi Waddah,
Sorry for the late reply, but I need some clarifications as column A seems to have no formulas in it!
If you can attach the worksheet here, that would be better.
Regards