Storing Active Cell Location


  • I want to store the start location before I start the macro,
    I am working in project, can someone please tell me where I am going wrong!?

    Sub Macro1()
        Dim StartRow As Integer
        Dim StartColumn As Integer
        StartRow = ActiveCell.Row
        StartColumn = Actuve.Column
     End Sub
    Thursday, July 04, 2013 3:09 AM


All replies

  • The following assumes you will employ several more Subs/Fuctions before completing your project code.
    You will need a variable that will still retain the location after you exit the sub.
    At the top of your module declare a Public variable and use it when you want...
    Option Explicit
    Public rngStartCell as Excel.Range

    Sub Macro1 
      Set rngStartCell = ActiveCell
      'do other things here
    End Sub

    Sub AlsoDoOtherStuff
      'other stuff here
    End Sub
    If needed rngStartCell.Row or rngStartCell.Column returns the row or column of the original ActiveCell.
    rngStartCell.Parent returns the Worksheet it is on.
    Jim Cone
    Portland, Oregon USA

    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Thursday, October 20, 2016 2:55 PM
    Thursday, July 04, 2013 3:39 AM
  • Hi Jim,
    I Just Tried SelectRange rngStartCell.Row, rngStartCell.Column
    Only to end up with an error.
    I want to have something like SelectRange rngStartCell.Row+1, rngStartCell.Column+1
    To take the active cell to a reference relative to the original active cell.
    Thursday, July 04, 2013 12:45 PM
  • You seem to be making up your own syntax :)

    In your original macro you had

        StartRow = ActiveCell.Row
        StartColumn = Actuve.Column

    notwithstanding the spelling of 'column' the correct syntax would be

        StartRow = ActiveCell.Row
        StartColumn = ActiveCell.Column

    to select a cell one row down, one column across using the method you envisaged then

        StartRow = ActiveCell.Row
        StartColumn = ActiveCell.Column
        Cells(StartRow + 1, StartColumn + 1).Select

    or simpler

       Dim rngStartCell As Range
       Set rngStartCell = ActiveCell
       rngStartCell.Offset(1, 1).Select

    Graham Mayor - Word MVP

    Thursday, July 04, 2013 2:31 PM
  • Apologies for the fat fingers!
    I like the steps above,
    I ran that exact code, except Range is not a type in vba.

    Friday, July 05, 2013 2:20 AM
  • Hi Mark,

    You say you're working in Project as in Microsoft Project?

    If so, try

    Dim OriginalTask As Task
        Set OriginalTask = ActiveCell.Task
        'Do other stuff
        EditGoTo ID:=OriginalTask.ID

    This method lets you use the OriginalTask object to access any of the originally selected task's info any time as well.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, July 05, 2013 2:21 AM
  • Hi Rod, thank you for the insight.
    The selected cell will go back to the originally selected cell when I follow your commands.

    I was wondering if it's possible to mark a cell location, store it, and refer to it later.
    I want to go down a list of ID numbers and pass them to a cell.
    But I can not pass them to the cell unless I have the ID. Ideally at some point I could
    say to the computer, remember this location, we will pass stuff to it later.

    Also I believe we have your book on order, no doubt that will make it easier to write VBA in MS Project
    Friday, July 05, 2013 5:11 AM
  • Hi Rod,
    Just wanted to let you know that I finished my little algorithm on Friday night,
    the code puts in milestones to headings,
    I'm really happy with it.

    Saturday, July 06, 2013 1:02 PM