Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    macro help needed (2000 9.0.3821 SR-1)

    Hi,

    I have set up some reports that need frequent updating. Sometimes the number only change by 1 and I would like to create a macro that would take the exisiting number, subtract 1 then move to the cell below, repeat the action 20 times.

    I have numbers like
    2091
    2116
    2135
    2152
    2161
    2167
    2174
    2183
    2185
    2194
    2206
    2210
    2218
    2220
    2223
    2229
    2232
    2240
    2246
    2249
    2251
    that all need to be reduced by 1. I tried doing a record macro, but it picked up 2091 then subtracted 1 so each cell ended up with 2090 rather than reducing each number by 1.

    Can anyone point me in the right direction of how to create such a macro?

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro help needed (2000 9.0.3821 SR-1)

    As you discovered, the macro recorder "hard wires" certain parameters. You can use that to your advantage if you have a place on your spreadsheet where you can drop 20 new formulas. Create formulas that subtract the "1" from your numbers. Then record a macro that copies the formulas over top of your existing number BUT using the Paste-Special > Values method. (you will find it in the menu). Using Paste Special will convert the formulas back to numbers which I think is what you want.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro help needed (2000 9.0.3821 SR-1)

    You might try something like this (changing the range/cell locations to fit):

    I entered the number "1" in cell B2
    and named the range of numbers to be changed: "TargetRange"

    <font color=blue>Private Sub AddOne()

    Range("B2").Copy
    Range("TargetRange").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False , Transpose:=False
    End Sub</font color=blue>
    Paul

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro help needed (2000 9.0.3821 SR-1)

    Sorry:
    Change Operation:=xlAdd to Operation:=xlSubtract

    Paul

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro help needed (2000 9.0.3821 SR-1)

    Even this will work:

    <font color=blue>Range("B2").Copy
    Range("TargetRange").PasteSpecial Operation:=xlSubtract</font color=blue>

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: macro help needed (2000 9.0.3821 SR-1)

    Hi,

    This fairly simple macro will subtract 1 from any selected cell, skipping over non-numeric (eg text) cells and converting any formulae in the range to numbers one less than the previous result. Any numbers stored as text will also be converted to a number, one less than the previous value.

    Sub Subtract()
    Dim oCell As Range
    On Error Resume Next
    For Each oCell In Selection.Cells
    oCell.Value = oCell.Value - 1
    Next
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: macro help needed (2000 9.0.3821 SR-1)

    Macropod has given you a macro that will do what you asked. However, you don't really need a macro, you can do this with standard Excel menu commands:

    1- Enter the value to be subtracted (added, multiplied, divided) into any empty cell on the worksheet. Then select Copy from the Edit menu.

    2- Select all of the cells from which this value is to be subtracted (added, multiplied, divided). Then select Paste Special from the Edit menu.

    3- In the resulting dialog box, put a tic mark next to Subtract (add, multiply, divide) in the Operation section of the dialog box.

    4- Click OK.
    Legare Coleman

Posting Permissions

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