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
Dec 14 2017 08:41 AM - edited Dec 14 2017 09:53 AM
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!
Mar 24 2019 07:13 PM
Solution
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").Select
to:
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
Apr 03 2019 11:50 PM
May 03 2019 12:49 AM
Thank you Haytham @Haytham Amairah
May 09 2019 05:25 AM
@Haytham Amairah Dear Haytham, would you be able to amend my code so that it works the same way as OP's please?
I have to amend the ActiveCell.End(xlDown) part so that the formula (in column N) stops at the last row that contains data in column M. Thank you in advance.
ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
ActiveCell.Select
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown))
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(ActiveCell).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Regards,
Mike
May 10 2019 01:43 AM
Hi Mike,
Please try this one:
Sub test()
ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
Selection.AutoFill Destination:=Range("N1:N" & Range("M" & Rows.Count).End(xlUp).Row)
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Hope that helps
Jun 25 2019 08:00 AM
Hi
I am trying to build a macro to unprotect sheet, autofill columns from above and protect sheet again. I am using below formula however it is giving error. Can anyone help please?
Sub sbUnProtectSheet()
ActiveSheet.Unprotect "9999"
Set SourceRange = ActiveSheet.Range("B10:H10")
Set fillRange = ActiveSheet.Range("B11:H39")
SourceRange.AutoFill Destination:=fillRange
ActiveSheet.Protect "9999", True, True
End Sub
Thanks
Anil
Jun 25 2019 10:40 AM
Hi,
It's better to post your question as a new conversation in the community.
If you can, provide us with a sample of the worksheet you're working on!
Regards
Jul 10 2019 08:57 AM
Hey Haytham,
I'm really hoping you can point me in the right direction as well.
I'm trying to do the same thing as OP, however I have blank rows above and to the left of my data and not sure if that is throwing it off. Below is a screenshot (had to switch all values to xxxx for privacy purposes), I want my Macro to create a new Column to the left of Column J in the screenshot, where a new Column J will be created that will house my formula beginning in the new Cell J6. I then want the Macro to extend this formula down the last row in any of the columns to the left of Column J, as they will all always be populated, however I have been tying everything to Column I in my troubleshooting.
Here is the snip of code that I know is causing the issue, with the problem rows likely being the three rows below my IF formula.
Sheets("Raw_Data").Select
ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "MEASURE OUTCOME"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A366")
ActiveCell.Range("A1:A366").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Met", _
Are you able to help me re-write this code to have the Macro fill the IF formula down to the last row of data rather than just the last cell in my current selection? The number of rows will change on a daily basis.
Thank you!
Jul 10 2019 09:03 AM
Hi
If you wee to create a Table instead, Insert > Table, then your formula would automatically be inserted as you add rows to the Table, without the need for any VBA code.
Jul 11 2019 10:10 AM - edited Jul 11 2019 10:11 AM
Hi,
Sorry about the late reply!
I think you need this one:
Sub FillFormulaDown()
'To check if the column is already exist
If Application.WorksheetFunction.IsFormula(Range("J5").Offset(1, 0)) Then
'skip
Else
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J5").Value = "MEASURE OUTCOME"
End If
Range("J5").Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
Selection.AutoFill Destination:=Range("J6:J" & Range("I" & Rows.Count).End(xlUp).Row)
ActiveCell.EntireColumn.AutoFit
End Sub
You will find it in the attached workbook.
Please test it and tell me what you thought.
Hope that helps
Jul 15 2019 05:40 PM
Hi Haytham,
I have used your code in my macro when it imports from a text file and reorganizes the records. As part of it, it is supposed to copy down rows of a table as below:
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
This works nicely but if there is a single line of record in the file, it copies the row but then it adds a second row, copies down the first row in column A and B then returns a VB Error as shown in the attached screenshot.
Any ideas how to deal with it?
Thanks
Nick
Jul 17 2019 07:58 PM
Jul 17 2019 08:06 PM
Sure, it is attached. Th eoriginal file is a tsv file but the upload tool would not accept tsv so I had to change it to csv. You will need to change it to tsv for the Excel file to read it.
Thank you
Jul 18 2019 08:50 AM
Hi,
This is what I got after opening your file:
Sorry, but the data isn't clear and I'm not sure what you're trying to do!
Based on your macro, you want to fill down Column A & B based on the number of rows in column E ??
Jul 18 2019 09:46 AM
Here are the macro steps:
This works fine except when there is a single in the source. I have attached both the Excel file and the different files that are used as the source.
The macro is created to import the file called current.tsv. We just swap the files we want to import by renaming them "current.tsv". So I provided one sith single line and another with multiple. The one with multiple works fine but single one returns error as the VBA seems to have a problem with it.
Curious situation. I hope this helps.
Jul 19 2019 06:26 AM
Hi,
After I reproduced this issue, I noticed that the error also occurs even if you have two records in the imported file.
If you have one or two rows in the imported file, there is no need to use the AutoFill method in the macro because all rows in the table are already filled.
If you use it in this case, there will be an error!
The solution is to make the code smart somehow to see if the imported file has less than three rows and if so, skip the autofill process to prevent the error.
This is what I suggest to overcome this issue:
Sub PrepFile()
'
' PrepFile Macro
'
'
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=current;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [current]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "current"
.Refresh BackgroundQuery:=False
End With
Range("K10").Select
ActiveCell.FormulaR1C1 = "Shipment ID"
Range("L10").Select
ActiveCell.FormulaR1C1 = "Shipment Name"
Range("B2").Select
Selection.Copy
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Copy
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:10").Select
Range("A10").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("K:L").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
If Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 1 Then
Range("A1").Select
Exit Sub
ElseIf Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 2 Then
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Exit Sub
End If
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
End Sub
Please replace your old macro with it.
Hope that helps
Aug 16 2019 04:21 AM
Aug 16 2019 04:49 AM
Mar 24 2019 07:13 PM
Solution
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").Select
to:
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