Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move Cursor via Macro (Excel XP)

    OK, this should be easy as pie but I'll be danged if I can find how to do it!!!!

    I enter a value into column B, any row. I move to the right to my required column and start my macro. My macro asks me for a value via the inputbox function, fine no worries. I assign this to a variable, "cost". Original, eh?

    I then want my macro to subtract the value in column B from the value I enter in the input box and enter it into the selected cell that I moved to. So the macro needs to basically say "This selected cell = COST minus {end}{left}".

    Then I want the cursor to go to the beginning of the row and down one cell - {home}{down}.

    Everywhere I look on the help deals with absolute references, so how do I complete this relatively simple process?

    Thanks in advance. Must admit, I feel foolish asking such simple questions.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move Cursor via Macro (Excel XP)

    You can use the Offset function for this.
    The active cell is (surprise, surprise) ActiveCell.
    The cell 2 rows down and 3 columns to the right is ActiveCell(2, 3).
    The row offset and column offset are positive for right and down, negative for left and up. Example:
    The cell above the active cell is ActiveCell.Offset(-1,0).

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Cursor via Macro (Excel XP)

    Hans, thanks for your reply.

    I understand the offset function, but the problem is that this is an absolute reference. I could be anywhere from column C to X, and I still want to subtract column B, so the offset function wont work. I want to subtract column B in the current row, then go to column A in the next row.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move Cursor via Macro (Excel XP)

    If I understand your question, you can use the cells object which takes a row, column value:

    <pre>activecell.value = cost - cells(activecell.row,2).value</pre>


    puts in the active cell the value in cost - value in col B (2nd col) of the current row (activecell.row)

    Then to select cell A of the next row:
    <pre>cells(activecell.row+1,1).activate</pre>



    Steve

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Cursor via Macro (Excel XP)

    Steve,

    That works perfectly, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •