?
Solved

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

Posted on 2011-09-13
15
Medium Priority
?
654 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 34

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 34

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 34

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 34

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 34

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 34

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 34

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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