pbarreda77
New Member
- Joined
- Aug 12, 2002
- Messages
- 25
below is code that looks for a date value in column a and inputs the cash payement due on the proper date in column b,then loops to find the next date that the bond will pay a coupon and is supposed to input the next coupon next to the next date. 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
The .Find statement is not finding the coupondate or is finding but not placing the coupon value when it loops
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
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
The .Find statement is not finding the coupondate or is finding but not placing the coupon value when it loops
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