asked on

Excel VBA to fill down from Variable named cell to Last Row

I have a workbook that I want to automate some actions.
Each Month the initial coluimn of data will be added to the right of the prior data using
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B3").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

After this I use a Vlookup function to get data from another book with:
Set rngFound = ActiveCell
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"

Here is my question, I need to go to the cell named "rngFound" and fill down (the Vlookup) in that column to (variable=) lastRow.
-I need to keep the range dynamic to allow for continual unmonitored automation.

Can somebody help me with the AutoFill Code??

Norie

If you want to find the last row and column when column A has data and the headers are in row 3 then put the formula in the next column try this.

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)"

Mitch Swetsky

ASKER

Thank you for your reply and suggestion.
I don't understand your comments.
I would like to keep the variables as I have them because I will be using them later in the code.

I tried to assign the monthly active cell as rngFound which will always contain the correct col.
The Vlookup would fill the cell named rngFound
Now I was trying to use the col of rngFound and fill down to the lastRow.

Norie

I've not really changed the variables you had, I did add LastCol to get rid of all the Select/Activate stuff.

rngFound will still point to the same cell/column.

The formula would go in the same cell and would be copied down to the last row.

Mitch Swetsky

ASKER

What is the code to copy that cell to the last row?
I get syntax errors since they are both variables.

Norie

Sorry I don't understand what syntax errors?

Are you being told they aren't recognised, not declared...?

This would be the whole thing with declarations for all the variables including rngFound.

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)"

Open in new window

The part that copies down to the last row is the Resize(LastRow-2), which increases the no of rows to copy down to.

Mitch Swetsky

ASKER

The errors I get are "Application Defined or object defined error"
I don't see where the code you posted fills down the column. Maybe I am not stating my intentions or I am not understanding your suggestion.
Could you comment your code so I understand what is supposed to occur?
Or review the code below.

The below code works but uses
Selection.AutoFill Destination:=Range("I3:I192")
which will change monthly

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

Open in new window

Norie

Well I'll try, might even add another variable to make it clearer, hopefully.

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)"

Open in new window

You can use rngFound anywhere else you need to in the code, it will be the same as rngFound in your code.

Mitch Swetsky

ASKER

Thank you so much for your patience and assistance.
My problem is line 13
In my sheet there are many ranges and data groups so when I selected B3 on my line 18 then I knew I would be in the correct area . (there is alot of data to the right on that sheet.)
If you believe it will still work, please help me by telling me what lines to replace with your suggestion.
Thanks again

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mitch Swetsky

ASKER

This works perfectly. And I appreciate the comments so I can learn too.
Thanks

Norie

No problem.

Just remember to mention any other data next time.:)

Mitch Swetsky

ASKER

I need to ask an additional question related to the solution provided.
I need to add IF(ISERROR
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
, 0 ,
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"

But I can't get the syntax correct, could you help?

Norie

Could you make this a new question?

You can relate it to this one I believe.

Mitch Swetsky

ASKER

sure

Mitch Swetsky

ASKER

Q# 27312178