PDA

View Full Version : How to create a dynamic range for vlookup based on active cell



Torrino1
04-03-2018, 08:51 AM
Below is my code thus far. I am working to input RTF data into Excel and then run a Vlook against that data to populate a new range each day. As you can see my current code is specific to a set column and I am hoping to write code to have the range be dynamic based on the active cell for the auto-fill.


Sheets("Apr 18").Select
'
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Monthly Data'!C1:C2,2,FALSE)"
Selection.AutoFill Destination:=Range("E3:E600")
Range("E3:E600").Selec
ActiveSheet.Range("$A$2:$HW$600").AutoFilter Field:=5, Criteria1:="#N/A"
Selection.ClearContents
ActiveSheet.Range("$A$2:$HW$600").AutoFilter Field:=5
Columns("C:AG").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Sheets("Monthly Data").Select
Columns("A:A").Select
Selection.QueryTable.Delete
Selection.ClearContents
Sheets("Apr 18").Select


End Sub

SamT
04-04-2018, 03:51 PM
Moderator Bump