| VBA Practice.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 4 | Issuer | Industry | Coupon | Notional | Callable | Maturity | Annual Interest | ||
| 5 | AMZN | Internet | 6.75 | 2,500,000 | No | 2023 | |||
| 6 | DELL | Hardware | 5.00 | 3,000,000 | Yes | 2024 | |||
| 7 | Ford | Auto | 6.00 | 1,000,000 | Yes | 2024 | |||
| 8 | GM | Auto | 6.25 | 3,000,000 | No | 2029 | |||
| 9 | GOOGL | Internet | 6.75 | 2,500,000 | Yes | 2023 | |||
| 10 | HWP | Hardware | 7.00 | 3,500,000 | Yes | 2025 | |||
| 11 | INTC | Semiconductors | 6.00 | 1,500,000 | No | 2026 | |||
| 12 | MSFT | Software | 6.50 | 2,000,000 | No | 2026 | |||
| 13 | NVDA | Semiconductors | 7.25 | 2,000,000 | Yes | 2029 | |||
| 14 | TSLA | Auto | 7.00 | 4,000,000 | Yes | 2025 | |||
Sheet2 | |||||||||
Hi All,
My code (for which I got some help on here) works fine, but I don't understand why it works.
My goal was to create formulas in the Annual Interest column that multiplies the Coupon * Notional * .01.
My Code below, with the line for the formula
>> Interest.Formula = "=" & FirstCoupon.Address(0, 0) & "*" & FirstNotional.Address(0, 0) & "* 0.01"
works well but I don't understand why. I referenced FirstCoupon which is Cell D5, but for the Annual Interest in cell H9, it knew to use D9 rather than D5. Which is correct, but that surprises me.
Is it the (0, 0) after the Address method that indicated that it should reference the cell from Row 9 rather than Row 5.
I would have thought I would of had to put in something more explicit that said 'use the row for Coupon from the same Row of the Interest formula'.
Or in other words, what does the (0, 0) mean? I know the first two parameters of the Address Property are RowAbsolute and ColumnAbsolute, but I thought it was a True/False designation.
expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
RowAbsolute Optional Variant. True to return the row part of the reference as an absolute reference. The default value is True.
ColumnAbsolute Optional Variant. True to return the column part of the reference as an absolute reference. The default value is True.
>>>>
Sub AnnualInterest()
Set LastRow = Cells.Find(What:="Issuer").End(xlDown)
Set FirstCoupon = Cells.Find(What:="Coupon").Offset(1)
Set Coupon = Range(FirstCoupon, Cells(LastRow.Row, FirstCoupon.Column))
Set FirstNotional = Cells.Find(What:="Notional").Offset(1)
Set Notional = Range(FirstNotional, Cells(LastRow.Row, FirstNotional.Column))
Set FirstInterest = Cells.Find(What:="Annual Interest").Offset(1)
Set Interest = Range(FirstInterest, Cells(LastRow.Row, FirstInterest.Column))
'Enter Formula
Interest.Formula = "=" & FirstCoupon.Address(0, 0) & "*" & FirstNotional.Address(0, 0) & "* 0.01"
End Sub
>>>>
Thank you for any help.
Alan
