Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Please help w/Macro to position cell focus (Office 2000 SP1)

    I have been trying to create a macro in a statistical spreadsheet (5 player team on each worksheet, entering scores under each player's name according to date of weekly competition, input scores consisting of 3 games in order, then calculate weekly total, season total, average, etc.). What I wish to accomplish is to make score entry easier by creating a macro that will take me from any cell in a row to the home cell of that row, then scroll down 36 cells, enter today's date in that cell, then move two cells to the right. The macro should end at this point. I would then enter the player's scores, then use the macro to once again move to the next player. Recording a macro doesn't work, because it always goes to the original cell that I started at, and ends in the same cell every time. I need some way of making the macro take me to the final cell I desire by looking at relevant position. I can send the spreadsheet if necessary. Please tell me this can be done!!! (and how to do it???) Thanks for the help.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Please help w/Macro to position cell focus (Office 2000 SP1)

    You can set your macro to record relative references rather than absolute references by using a tool button on the toolbar that pops up when you are recording a macro. Usually there are two buttons, thr first stops recording but th second button (to the right - seee attached graphic) toggles relative addressing. Try it and see if it helps.

    Andrew C
    Attached Images Attached Images

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help w/Macro to position cell focus (Office 2000 SP1)

    The macro below should do what you asked:

    <pre>Public Sub GoToNext()
    Dim lCRow As Long
    lCRow = ActiveCell.Row - 1
    ActiveSheet.Range("A1").Offset(lCRow + 36, 0) = Date
    ActiveSheet.Range("A1").Offset(lCRow + 36, 2).Select
    End Sub
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help w/Macro to position cell focus (Office 2000 SP1)

    I knew there had to be a way - I set this up and it works perfectly! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> LegareColeman, you are my new hero - thanks a big bunch for your help! <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help w/Macro to position cell focus (Office 2000 SP1)

    Andrew - that button doesn't appear when I start recording any macro's. Is there a way to add it in? - Rod

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please help w/Macro to position cell focus (Office 2000 SP1)

    Right click on the menu bar area and display the "Stop Recording" menu bar.

    Ian.

Posting Permissions

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