Solved

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

Posted on 2011-09-13
15
494 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
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
Comment Utility
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
Comment Utility
This works perfectly. And I appreciate the comments so I can learn too.
Thanks
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
No problem.

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

Author Comment

by:Mswetsky
Comment Utility
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
Comment Utility
Could you make this a new question?

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

Author Comment

by:Mswetsky
Comment Utility
sure
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
Q# 27312178
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now