Ivan if your around help? please

[pbarreda77]

below is code that looks for a date value in column a and inputs the cash payement due on that date in column b, finds the next date that the bond will pay a coupon and is supposed to input the next coupon value. I can't get the .find statment to input anything after the first coupon. However the next coupon date is being correctly calulated as per the msg box display and the correct number of coupons is also functioning.

I have defaulted the column number to "z" but in the finished modual the z value would be the z value of the current column which is the next empty coulmn to the right.

the spread sheet looks like this for a bond paying 3000 on feb 2002


column a column b
a6 Jan 2002
a7 Feb 2002 3000
a8 Mar 2002

and so on and so on down the column





Public Sub CommandButton1_Click()

Dim couponDate ' date coupon is paid in cell (4, currcolumn)
Dim couponCash As Integer ' amount of coupon in cell (3, currcolumn)
Dim couponNum As Integer ' number of coupon payments
Dim y As Integer ' incremental counter to find the next couponDate
Dim newcouponDate ' the next coupon date
Dim x As Integer ' counter
Dim z As Integer ' counter for next empty column
Dim intervaltype As String
Dim paymentsPerYear As Integer

' Get the z value representing the column number


z = 2

' lookup values

couponNum = Cells(2, z).Value
couponCash = Cells(3, z).Value
couponDate = Format((Cells(4, z).Value), Cells(4, z).NumberFormat)
paymentsPerYear = Cells(1, z).Value

' msg box check that values are loaded

msg = couponCash & vbCrLf & couponDate

MsgBox msg


' sequence to calculate the next coupon date


Do Until x = couponNum

' sequence to look in column a for matching coupon dates and enter coupon cash in
' the correct row

With Worksheets(1).Range("a1:a500")
Set c = .Find(couponDate, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 1).Value = couponCash
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address<> firstAddress
End If
End With

Select Case paymentsPerYear
Case 12
y = 1
Case 4
y = 3
Case 2
y = 6
Case 1
y = 12
Case Else
Exit Sub
End Select

' Find next coupon date

intervaltype = "m"

couponDate = Dateadd(intervaltype, y, couponDate)

msg = couponDate

MsgBox msg

x = x + 1
If x = couponNum Then Exit Do
Loop



End Sub
This message was edited by pbarreda77 on 2002-08-27 07:55