Replies

I’ve never worked with Autofilters in VBA so I’ve never seen those objects, properties, or methods, but that function is way cool.

I’ve never worked with Autofilters in VBA so I’ve never seen those objects, properties, or methods, but that function is way cool.

Hans,
My VBA doesn’t seem to recognize calculatefull. I’ve previously looked for a VBA equivalent to ctrl-alt-f9, but I’ve never found one.

The TIMER function is cleaner than using NOW. However, it seems TIMER won’t work if the execution of your code is executing at midnight.

Hans,
My VBA doesn’t seem to recognize calculatefull. I’ve previously looked for a VBA equivalent to ctrl-alt-f9, but I’ve never found one.

The TIMER function is cleaner than using NOW. However, it seems TIMER won’t work if the execution of your code is executing at midnight.

Well, believe it or not, I have been following this discussion. While it seems to have drifted a little off topic, I believe I understand the arguments for declaring variables. I do have some computer science in my academic background, but I have never been a programmer. I’m the kind of guy that everyone else comes to for Excel help. Since I don’t normally create “applications” with dozens and dozens of lines of code which are hard to debug, I consider the arguments in favor of Option Explicit to be a bit esoteric and the benefits minimal. I think I write well-organized code and I don’t see how declaring variables would improve that. As one poster said, a real programmer would know all the needed variables before writing a single line of code. That may be true, but for my scale of project, I don’t think that kind of upfront analysis is time well-spent. The next time I embark on something with a larger scale, I’ll declare my variables. For my usual utility macros for PERSONAL.XLS or custom functions, I think I’ll continue in my slothful ways.

I would add another benefit to declaring variables: you automatically get your intended cApiTaliZATIon each time you use the variable.

Thanks to everyone for your input.

Well, believe it or not, I have been following this discussion. While it seems to have drifted a little off topic, I believe I understand the arguments for declaring variables. I do have some computer science in my academic background, but I have never been a programmer. I’m the kind of guy that everyone else comes to for Excel help. Since I don’t normally create “applications” with dozens and dozens of lines of code which are hard to debug, I consider the arguments in favor of Option Explicit to be a bit esoteric and the benefits minimal. I think I write well-organized code and I don’t see how declaring variables would improve that. As one poster said, a real programmer would know all the needed variables before writing a single line of code. That may be true, but for my scale of project, I don’t think that kind of upfront analysis is time well-spent. The next time I embark on something with a larger scale, I’ll declare my variables. For my usual utility macros for PERSONAL.XLS or custom functions, I think I’ll continue in my slothful ways.

I would add another benefit to declaring variables: you automatically get your intended cApiTaliZATIon each time you use the variable.

Thanks to everyone for your input.

You might try Range(“A”+format(i,”#”)+”:C”+format(i,”#”))

You might try Range(“A”+format(i,”#”)+”:C”+format(i,”#”))

Well, this is pretty ugly but maybe this will work for you.

I typed 1/1/2003 in A1. In cell B1, I typed =”=text(“&CELL(“address”,A1)&”,””mm/yyy””)”

A1 looks like: 1-Jan . B1 looks like: =text($A$1,”mm/yyy”)

If I now save the workbook as a csv file and reopen it in Excel, A1 still looks like: 1-Jan. B1 looks like: 01/2003

I think you’re not just copying the last occurrence into sheet2!a1.c1, but actually copying all occurences. So naturally you only see the last one.

I haven’t tested this but I think you can just modify your original code as follows:

Private Sub cmdSendMac_Click()
Dim RowCount As Integer
Dim Choice As Integer
Dim i As Integer
RowCount = Range(“A1”).End(xlDown).Row

Range(“A1:C1”).Select
i=0
For Choice = 1 To RowCount

ActiveCell.Offset(1, 0).Select

If ActiveCell.Font.Color = RGB(0, 0, 0) And ActiveCell.Value “” Then
i=i+1
ActiveCell.Copy Destination:=Worksheets(“Sheet2”).Range(“A”+i+”:C”+i)

Else

End If

Next Choice

End Sub

I think you’re not just copying the last occurrence into sheet2!a1.c1, but actually copying all occurences. So naturally you only see the last one.

I haven’t tested this but I think you can just modify your original code as follows:

Private Sub cmdSendMac_Click()
Dim RowCount As Integer
Dim Choice As Integer
Dim i As Integer
RowCount = Range(“A1”).End(xlDown).Row

Range(“A1:C1”).Select
i=0
For Choice = 1 To RowCount

ActiveCell.Offset(1, 0).Select

If ActiveCell.Font.Color = RGB(0, 0, 0) And ActiveCell.Value “” Then
i=i+1
ActiveCell.Copy Destination:=Worksheets(“Sheet2”).Range(“A”+i+”:C”+i)

Else

End If

Next Choice

End Sub

Newtonian iterations typically reach an answer within very few iterations (i.e. 5). If Excel hasn’t reached an answer in 100 iterations, it’s never going to get one.

It’s not that there’s a threshhold of X at which point the iteration process fails. You have a “small” number and that makes the iteration process susceptible to failure.

If you really want to use the YIELD function, just change the formula in F3 from =YIELD(B3,C3,0,D3,1,2,1) to =YIELD(B3,C3,0,100*D3,100,2,1) and copy that down.

Newtonian iterations typically reach an answer within very few iterations (i.e. 5). If Excel hasn’t reached an answer in 100 iterations, it’s never going to get one.

It’s not that there’s a threshhold of X at which point the iteration process fails. You have a “small” number and that makes the iteration process susceptible to failure.

If you really want to use the YIELD function, just change the formula in F3 from =YIELD(B3,C3,0,D3,1,2,1) to =YIELD(B3,C3,0,100*D3,100,2,1) and copy that down.

If you multiply your price by 100 and set your redemption value to 100, you get a valid answer.

In the Excel help, it says the YIELD function uses Newtonian iterations to get its answer. Newtonian iterations operate by starting with an initial guess at the yield. It then calcs a price from the cashflows you’ve specified. If the calc’d price is different from your specified price, it determines whether to adjust its guess up or down. It will continue this process until the calc’d price is “sufficiently close” to the specified price. The criteria for sufficiently close is usually abs(calc’d price-specified price)/specified price. So if specified price is small (as it is in your original example), the allowable error is very small and Excel may not be able to reach a satisfactory answer prior to 100 iterations.

BTW, because your examples do not have any coupon payments, the formula I’ve added in my attachment presents a closed form solution (i.e. can be directly calc’d without going through an iterative process which always introduces some error). While we’re definitely splitting hairs, my formula is more accurate than yours.

This was done in Excel 2002 SP-1.

If you multiply your price by 100 and set your redemption value to 100, you get a valid answer.

In the Excel help, it says the YIELD function uses Newtonian iterations to get its answer. Newtonian iterations operate by starting with an initial guess at the yield. It then calcs a price from the cashflows you’ve specified. If the calc’d price is different from your specified price, it determines whether to adjust its guess up or down. It will continue this process until the calc’d price is “sufficiently close” to the specified price. The criteria for sufficiently close is usually abs(calc’d price-specified price)/specified price. So if specified price is small (as it is in your original example), the allowable error is very small and Excel may not be able to reach a satisfactory answer prior to 100 iterations.

BTW, because your examples do not have any coupon payments, the formula I’ve added in my attachment presents a closed form solution (i.e. can be directly calc’d without going through an iterative process which always introduces some error). While we’re definitely splitting hairs, my formula is more accurate than yours.

This was done in Excel 2002 SP-1.