Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Increasing a cell value by one via macro

    Hi everyone:

    I'm a complete Excel idiot [img]/w3timages/icons/blush.gif[/img] so please forgive me if there is an obvious answer to my question. What I am trying to do in this spreadsheet (which I've attached if it helps anyone) is track the statistical information of a tennis game. My aim is to enable the coach watching the game in real time to be able to use a keystroke associated with a macro to increase the value of a cell by one. I.e. pressing CTRL+A will increase the number of aces by one in cell C3, CTRL+F increases forehand winners by one in cell C6.

    I was just barely able to set up totals and percentages for all the statistical categories we'd like to track but now I'm stuck and I'm not getting anywhere with Excel's help files in terms of increasing the value of a cell. I have some experience with Word macros, but I'm lost in this realm of tables and formulas. Can anyone help me?

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

    Re: Increasing a cell value by one via macro

    Keely: The following VBA Subroutine will add one to cell C3 when executed:

    <pre>Public Sub AddOne()
    Range("C3").Value = Range("C3") + 1
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increasing a cell value by one via macro

    Keely,

    I'd be inclined to have some pushbuttons to do what you want- and then assign keyboard shortcuts to them- so that they can be used either with a mouse or with the keyboard shortcut (not relying too much on the scorer's ability with computers).

    To add a button:

    .Select View, Toolbars, Control Toolboc.
    .Select the icon which looks like a button, then draw the button
    .Right click on the control
    .Put something appropriate in the caption field
    .Put a letter in the "accelerator" property. That will execute when you press "Alt" plus the key- eg, if you put "S" in the accelerator field, it will execute when you press Alt + S
    .Close the properties box, and double click on the control. You will be taken into the macro editor- a subroutine with a name like "Private Sub CommandButton1_Click()" will already be set up for you. In that routine, put your code- Legare's code is a good starting point
    .When you are ready to test, click on the button in the control toolbox which looks like a blue triangular set square. The will flip you from "design" mode for your control to "execute" mode. You can change back to design mode at any time.

    You can also add things like option buttons- which might be useful when scoring- eg, "Agassi serving", "Rafter serving" (guess what's on the TV in Australia right now!)

    If you later wnat to print the page without the buttons, you can either set up a print area which does not include the buttons, or change the "PrintObject" property for the control to false.

    I've just skimmed over things- I hope that this is a good start for you
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increasing a cell value by one via macro

    Thanks both of you for the ideas. I will give them a shot while watching the taped coverage of the Rafter/Agassi match - during the commercials, of course! - and let you know how it goes.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Increasing a cell value by one via macro

    Keely,

    I had the same problem with something I was doing a week or two ago. The problem in general is that you can't add 1 (or anything else) to the cell directly, to the best of my knowledge, w/o going to a macro. You get a circular reference error.

    So with my little knowledge of excel macros, here's a starting point where cell(7,1) [ie, row=7, col=A] is the cell to be increased.

    Sub add1()
    '
    ' add1 Macro
    '
    Dim a As Integer
    a = Cells(7, 1)
    Debug.Print a
    a = a + 1
    Cells(7, 1) = a
    '
    End Sub

    I know you'll get lots of other answers too. The embellishments you'd want would probably include:
    - select any cell to increase (obviously) so the code has to look for the addr of the cell currently selected (assuming that's the one to be increased)
    - probably don't want the user to have to go to tools | macros etc to invoke the macro. A button would help and is not hard to do.

    fred
    [img]/w3timages/icons/smile.gif[/img]

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increasing a cell value by one via macro

    Thank you so much Geoff, Legare and Fred! Unbelievably, I have been able to construct the most kick-ass stats sheet I could have imagined, complete with both buttons and keystroke shortcuts to add or subtract from the statistical category. I am over the moon! [img]/w3timages/icons/king.gif[/img] Thanks again,

Posting Permissions

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