looping ... date formula ...

[jer]

I am not sure if this is for here or worksheet function
group so I am posting twice. I am relatively new to
writing code and would greatly appreciate any help with
this. I have inherited a worksheet with 12 columns
(comprising payment amounts, reducing balances,
categories, etc). Column 11 holds a reference number and
against this reference number there are 48 monthly
payments to be made. Payments are to be made on the first
of every month. Rather than create the formula and drag
it down, the spreadsheet contains 100 reference numbers, I
was looking for a way to to this
automatically. I have used the following code but I keep
getting #NAME? error. cells(9,2) should be 01-June-02,
cells(9,3) should be 01-July-02, etc I am guessing that
the problem is how I am referencing the date field. I am
also adding a sample of th worksheet

1 2 3 4
5 6 7 8
9 10 11 12

$165,000.00 ($2,759.22) $165,000.00 20
$1,213.70 $1,545.52 $163,454.48
01-May-02 P28 8202 446
$163,454.48 ($4,138.83) $159,315.65 30
$1,757.82 $2,381.01 $161,073.47 1
#NAME? P28 8202 446
$161,073.47 ($4,138.83) $156,934.64 30
$1,731.55 $2,407.28 $158,666.19 2
#NAME? P28 8202 446
$158,666.19 ($4,138.83) $154,527.36 30
$1,704.99 $2,433.84 $156,232.35 3
#NAME? P28 8202 446


Dim cell As Range
Set cell = ActiveWorkbook.Worksheets("Sheet1").Cells
(3, 12)

Do Until IsEmpty(cell.Value)
If cell.Offset(0, -1) = 8202# Then

Do Until cell.Offset(0, -5) = 0 'Balance of
payments due

cell.Offset(0, -3).Value = "=Date(Year
(cell.Offset(-1, -3)), Month(cell.Offset(-1, -3)) + 1, Day
(cell.Offset(-1, -3)))"

Set cell = cell.Offset(1, 0)
Loop
End If
Set cell = cell.Offset(1, 0)
Loop

Any suggestions. Any help would be greatly appreciated.
Thanks in advance
jer

 

[Jim Rech]

You might try replacing the part of your code that enters the formula with
this:

With Cell
.Offset(0, -3).Value = "=Date(Year(" & .Offset(-1, -3).Address _
& "),Month(" & .Offset(-1, -3).Address _
& ") +1, Day(" & .Offset(-1, -3).Address & "))"
End With

Your code enters everything to the right of the "=" as literal text.
Literal VBA code like "cell.Offset(-1, -3))" has no meaning in a worksheet
cell. You have to enter what that code evaluates to.

 

[jer]

Jim, Thank much ... works like a charm...
have a great day!!!
jer