Solved

How can I get the range of the activecell and assign it to a varable

Posted on 2011-09-12
2
286 Views
Last Modified: 2012-05-12
I need to revisit the same cell later in my sub but am having a hard time setting a var = activecell
I want to navigate to the first blank col then run vlookup from other open sheet and go back to active cell and fill down to the last row.
Code below is just a snip of my project.
Sheets("Actual & Flexed Workload").Activate 
 lastRow = Range("A" & Rows.Count).End(xlUp).Row  'Get last row to fill down
     Range("B3").Select          'Get consistent begin cell
    ' Most likely will be january and each month I will get new month column
     
     Selection.End(xlToRight).Select    'move to end of row to add new month data in that col
     ActiveCell.Offset(0, 1).Select
    ' I need to get this cell range and save as rngFound so after getting data from another sheet I can come back and paste in this column
  
'Poor effort of catching active cell
 AFWrow = ActiveCell.Row
 AFWcol = ActiveCell.Column
 rngFound = Range("R" & AFWrow & "C" AFWcol ")
   
 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R +lastRow+ C29,6,FALSE)"

    Windows("2011 U.S. Financial Plan.xls").Activate
    rngFound.Select
    
    Selection.AutoFill Destination:=Range(rngFound)
    Range(rngFound).Select

Open in new window

0
Comment
Question by:Mswetsky
2 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
dim aVar as range and then set aVar to the activecell via

set aVar = activecell

Chris
0
 
LVL 1

Author Closing Comment

by:Mswetsky
Comment Utility
Thanks, for you quick & simple to understand answer
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

763 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

5 Experts available now in Live!

Get 1:1 Help Now