in VBA, enter formula for product

[Alan_CT06]

I have the following spreadsheet

A B C D

1 Issuer Coupon Notional Interest
2 Ford 6.00 1,000,000
3 GM 6.50 2,000,000
4 MSFT 7.00 3,000,000

I would like to enter a formula in column D that will multiply column B * Column C * .01

I am able to create variable for the ranges of the Coupon and the Notional and believe there is some way I can enter formulas for the whole range of Interest (D2:D4) that would multiply B*C*.01.

Below is my code so far.

Thank you for your help.

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:="Interest").Offset(1)
      
    Set Interest = Range(FirstInterest, Cells(LastRow.Row, FirstInterest.Column))
   

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Does this do what you want? Test with a copy of your workbook.

Sub Insert_Formula()
  Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=RC[-2]*RC[-1]*.01"
End Sub

 

[Alan_CT06]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Does this do what you want? Test with a copy of your workbook.

Sub Insert_Formula()
  Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=RC[-2]*RC[-1]*.01"
End Sub

Peter,

Thank you for the reply and I will try the XL2BB next time.

One questions, in case I move my columns, is there a way I can modify this to reference the column by the range name, so rather than
"=RC[-2]*RC[-1]*.01", using the [-2] and [-1]

the code would define the column by the Notional and Coupon ranges defined in my code?

 

[Peter_SSs]

in case I move my columns, is there a way I can modify this to reference the column by the range name, so rather than
"=RC[-2]*RC[-1]*.01", using the [-2] and [-1]

the code would define the column by the Notional and Coupon ranges defined in my code?

Sure. This is not quite how I would do it if starting from scratch, but using your existing code as a basis, try this.
I have commented out some of the code as it is not needed for this particular task, though you may still want it for something else that might be in your code.

Sub AnnualInterest_v2()
  Dim LastRow As Range, FirstCoupon As Range, FirstNotional As Range
  Dim FirstInterest As Range, Interest As Range
'  Dim Coupon As Range, Notional As Range
  
   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:="Interest").Offset(1)
     
   Set Interest = Range(FirstInterest, Cells(LastRow.Row, FirstInterest.Column))
   
   Interest.Formula = "=" & FirstCoupon.Address(0, 0) & "*" & FirstNotional.Address(0, 0) & "*0.01"

End Sub

 

[Alan_CT06]

Sure. This is not quite how I would do it if starting from scratch, but using your existing code as a basis, try this.
I have commented out some of the code as it is not needed for this particular task, though you may still want it for something else that might be in your code.

Sub AnnualInterest_v2()
  Dim LastRow As Range, FirstCoupon As Range, FirstNotional As Range
  Dim FirstInterest As Range, Interest As Range
'  Dim Coupon As Range, Notional As Range
 
   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:="Interest").Offset(1)
    
   Set Interest = Range(FirstInterest, Cells(LastRow.Row, FirstInterest.Column))
  
   Interest.Formula = "=" & FirstCoupon.Address(0, 0) & "*" & FirstNotional.Address(0, 0) & "*0.01"

End Sub

Yes, that works perfectly, thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.