Results 1 to 7 of 7

20040608, 02:55 #1
 Join Date
 Feb 2001
 Posts
 163
 Thanks
 2
 Thanked 0 Times in 0 Posts
macro help needed (2000 9.0.3821 SR1)
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

20040608, 04:04 #2
 Join Date
 Jan 2001
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 631
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: macro help needed (2000 9.0.3821 SR1)
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 PasteSpecial > 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

20040608, 04:25 #3
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: macro help needed (2000 9.0.3821 SR1)
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

20040608, 05:02 #4
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: macro help needed (2000 9.0.3821 SR1)
Sorry:
Change Operation:=xlAdd to Operation:=xlSubtract
Paul

20040608, 05:06 #5
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: macro help needed (2000 9.0.3821 SR1)
Even this will work:
<font color=blue>Range("B2").Copy
Range("TargetRange").PasteSpecial Operation:=xlSubtract</font color=blue>

20040608, 06:53 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 Posts
Re: macro help needed (2000 9.0.3821 SR1)
Hi,
This fairly simple macro will subtract 1 from any selected cell, skipping over nonnumeric (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 SubCheers,
Paul Edstein
[MS MVP  Word]

20040608, 16:05 #7
 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 SR1)
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