I have a worksheet and it contains one row of information that I want to
autofill down and subtract numbers like below:



A B

Row1 Widget 500



Now I want to make a macro/code that will fill down until the number in row
B equals 1. Row B can change, in the example it is 500, but it could be
272, 25, 1000 etc...



So after running the macro the spreadsheet would look like below:



A B

Row1 Widget 500

Row2 Widget 499

Row3 Widget 498



I know there is an autofill method, but I just want the user to put in the
information in the first row, execute the macro/code and let Excel do the
work. This information will then be pulled into a Mail Merge Document and
labels will be printed, in the above example, there will be 500 lables,
Widget 1 through Widget 500. Any assistance in coding the Excel part would
be greatly appreciated.

Hi Wally,
writing this on the hop so will need to test it.
-------------------------------------------------------
sub testfirst()
application.screenupdating = false
range("b2").select
do until activecell.offset(-1,0).value = 1
activecell.value=activecell.offset(-1,0).value-1
activecell.offset(0,-1).value =activecell.offset(-1,-1).value
activecell.offset(1,0).select
loop
range("a1").select
application.screenupdating = true
end sub
----------------------------------------------------------
regards
Pete


--
(][ This Email has been scanned by Norton AntiVirus. ][)
"Wally Steadman" wrote in message
...
I have a worksheet and it contains one row of information that I want to
autofill down and subtract numbers like below:



A B

Row1 Widget 500



Now I want to make a macro/code that will fill down until the number in
row
B equals 1. Row B can change, in the example it is 500, but it could be
272, 25, 1000 etc...



So after running the macro the spreadsheet would look like below:



A B

Row1 Widget 500

Row2 Widget 499

Row3 Widget 498



I know there is an autofill method, but I just want the user to put in the
information in the first row, execute the macro/code and let Excel do the
work. This information will then be pulled into a Mail Merge Document and
labels will be printed, in the above example, there will be 500 lables,
Widget 1 through Widget 500. Any assistance in coding the Excel part
would
be greatly appreciated.

Wally,

This should get you started...
'----------------------------------
Sub DoNotRespondToUnsolicitedAdvertising()
'Jim Cone - December 27, 2004
Dim rngStart As Excel.Range
Dim rngLast As Excel.Range
Dim lngStartNum As Long

'Assumes correct cell is selected and a
'name is filled in to the left.
lngStartNum = Val(VBA.InputBox(vbCr & "Enter the quantity", _
" Wally's List", "Be reasonable"))
If Val(lngStartNum) 2 Then
Set rngStart = ActiveCell
rngStart.Value = lngStartNum
rngStart(2, 1).Value = lngStartNum - 1
Else
GoTo QuitHere
End If

Set rngLast = rngStart(lngStartNum, 1)
Range(rngStart, rngStart(2, 1)).AutoFill _
Range(rngStart, rngLast)

Range(rngStart, rngLast).Offset(0, -1).Value = _
rngStart(1, 0).Value
Exit Sub

QuitHe
Set rngStart = Nothing
Set rngLast = Nothing
End Sub
'--------------------------

Regards,
Jim Cone
San Francisco, USA


"Wally Steadman" wrote in message
...
I have a worksheet and it contains one row of information that I want to
autofill down and subtract numbers like below:
A B
Row1 Widget 500
Now I want to make a macro/code that will fill down until the number in row
B equals 1. Row B can change, in the example it is 500, but it could be
272, 25, 1000 etc...
So after running the macro the spreadsheet would look like below:
A B
Row1 Widget 500
Row2 Widget 499
Row3 Widget 498
I know there is an autofill method, but I just want the user to put in the
information in the first row, execute the macro/code and let Excel do the
work. This information will then be pulled into a Mail Merge Document and
labels will be printed, in the above example, there will be 500 lables,
Widget 1 through Widget 500. Any assistance in coding the Excel part would
be greatly appreciated.

Wally,

Small correction...
Delete the "Exit Sub" line - 5th code line from bottom.

Jim Cone


"Jim Cone" wrote in message
...
Wally,
This should get you started...
'----------------------------------
Sub DoNotRespondToUnsolicitedAdvertising()
'Jim Cone - December 27, 2004
Dim rngStart As Excel.Range
Dim rngLast As Excel.Range
Dim lngStartNum As Long
'Assumes correct cell is selected and a
'name is filled in to the left.
lngStartNum = Val(VBA.InputBox(vbCr & "Enter the quantity", _
" Wally's List", "Be reasonable"))
If Val(lngStartNum) 2 Then
Set rngStart = ActiveCell
rngStart.Value = lngStartNum
rngStart(2, 1).Value = lngStartNum - 1
Else
GoTo QuitHere
End If
Set rngLast = rngStart(lngStartNum, 1)
Range(rngStart, rngStart(2, 1)).AutoFill _
Range(rngStart, rngLast)
Range(rngStart, rngLast).Offset(0, -1).Value = _
rngStart(1, 0).Value
Exit Sub '*** Delete this line
QuitHe
Set rngStart = Nothing
Set rngLast = Nothing
End Sub
'--------------------------
Regards,
Jim Cone
San Francisco, USA