?
Solved

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

Posted on 2011-09-13
15
Medium Priority
?
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 35

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 35

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 35

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 35

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 35

Accepted Solution

by:
Norie earned 2000 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 35

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 35

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

650 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