Solved

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

Posted on 2011-09-13
15
568 Views
Last Modified: 2012-05-12
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??
0
Comment
Question by:Mswetsky
  • 8
  • 7
15 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36529454
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)"
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36529506
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 36529549
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.

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Mswetsky
ID: 36529677
What is the code to copy that cell to the last row?
I get syntax errors since they are both variables.
0
 
LVL 33

Expert Comment

by:Norie
ID: 36529775
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.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36529998
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

0
 
LVL 33

Expert Comment

by:Norie
ID: 36530193
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.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36530292
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
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36530347
Well if you need to start at B3 then change to this.
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)"

Open in new window


There were actually a couple of errors in the last code I posted, so that should fix them as well.
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 36530508
This works perfectly. And I appreciate the comments so I can learn too.
Thanks
0
 
LVL 33

Expert Comment

by:Norie
ID: 36530564
No problem.

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

Author Comment

by:Mswetsky
ID: 36549354
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?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36549470
Could you make this a new question?

You can relate it to this one I believe.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36549507
sure
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36549530
Q# 27312178
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question