Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
Dim rngFound As Range
Dim LastCol As Long
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(3, Columns.Count).end(xlToLeft).Column +1
Set rngFound = Cells(3, LastCol)
rngFound.Resize(LastRow-2).FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
The part that copies down to the last row is the Resize(LastRow-2), which increases the no of rows to copy down to.
Sub Macro1()
Dim LastRow As Long
Dim rngFound As Range
Dim LastCol As Long
' Open initial Workbooks Financial & P01s start with Currmonth
ChDir "C:\Users\US889923\Desktop\PLGFP"
Workbooks.Open Filename:= _
"C:\\Desktop\PLGFP\P01Currentmonth.csv"
Workbooks.Open Filename:= _
"C:\\Desktop\PLGFP\2011 U.S. Financial Plan.xls"
'Create cur month OGM wkld in Fimnancial Workbook sheet= Actual & Flexed Workload'
Windows("2011 U.S. Financial Plan.xls").Activate
Sheets("Actual & Flexed Workload").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B3").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
' ID variable to go back for Fill-Down
Set rngFound = ActiveCell
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
Windows("2011 U.S. Financial Plan.xls").Activate
rngFound.Select
Selection.AutoFill Destination:=Range("I3:I192")
rngFound.Select
Windows("P01Currentmonth.csv").Activate
ActiveWindow.Close
End Sub
Dim rngFound As Range
Dim rngFormula As Range ' this will be a reference to the range the formulas will go in
Dim LastCol As Long ' for the last column
Dim LastRow As Long ' for th last row
' first get the last row with data based on column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' now get the last column with data in row 3
LastCol = Cells(3, Columns.Count).End(xlToLeft).Column
' this is the next empty cell, it's the same as ActiveCell in your code
Set rngFound = Cells(3, LastCol + 1)
' this will be the range the formula will go in, from rngFound down to the last row in the same column
' Cells(LastRow, LastCol) refers to the last cell in the column
Set rngFormula = Range(rngFound, Cells(LastRow, LastCol))
' now we put this formula in
rngFormula.Resize(LastRow - 2).FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
You can use rngFound anywhere else you need to in the code, it will be the same as rngFound in your code.
Dim rngFound As Range
Dim rngFormula As Range ' this will be a reference to the range the formulas will go in
Dim LastCol As Long ' for the last column
Dim LastRow As Long ' for th last row
' first get the last row with data based on column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' now get the last column with data in row 3
LastCol = Range("B3").End(xlToRight).Column
' this is the next empty cell, it's the same as ActiveCell in your code
Set rngFound = Cells(3, LastCol + 1)
' this will be the range the formula will go in, from rngFound down to the last row in the same column
' Cells(LastRow, LastCol) refers to the last cell in the column
Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))
' now we put this formula in
rngFormula.FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
How to account for holiday's & weekends in my spreadsheet? | 4 | 28 | |
Find and Replace Function not working in Excel | 13 | 42 | |
VBA copy column paste as value | 5 | 20 | |
Excel error #DIV/0! | 7 | 18 |
Join the community of 500,000 technology professionals and ask your questions.