problem with .find within a loop

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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,144,330
Messages
5,723,749
Members
422,513
Latest member
Meathead2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top