Search code examples
excelvbaformulaxirr

Relative Range in the Row of a Formula

I have been struggling with a macro for over a month now. The goal is to break up data by security identifier (in column C) by inserting a row every time the value in the column changes. Afterwards, I want to insert an XIRR formula in the blank row that was inserted. The issue is that every security has a different amount of rows, and I cannot get the row count to become dynamic in the XIRR formula. Here is the code I have been able to put together:

 Dim lRow As Long

 Dim kRow As Long

 Dim RowCount As Integer
 
 
 For lRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 2 Step -1

 If Cells(lRow, "C") <> Cells(lRow - 1, "C") Then Rows(lRow).EntireRow.Insert
 
If Cells(lRow, "A").Value = "" Then Cells(lRow, "A").Value = "IRR"
    
 Next lRow
  
  
 For kRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1

 RowCount = Range(Selection, Selection.End(xlUp)).Count - 1
 
 If Cells(kRow, "A").Value = "IRR" Then Cells(kRow, "A").Offset(0, 5).Select

 Selection.Value = RowCount

 ActiveCell.FormulaR1C1 = "=XIRR(R[-RowCount]C[0]:R[-1]C[0],R[-RowCount]C[-1]:R[-1]C[-1])"


 Next kRow

I know the RowCount variable is counting the correct amount of rows, but the XIRR formula is not accepting the variable as an input to the row count. I get a break at this step of the macro every time, no matter how many different iterations I try to make the formula dymanic. Could someone please help understand what I am doing wrong?

Thank you!

Solution

  • As stated by @BigBen in the comments, you need to build your rowCount variable into the string for your formula.

    ActiveCell.FormulaR1C1 = "=XIRR(R[-RowCount]C[0]:R[-1]C[0],R[-RowCount]C[-1]:R[-1]C[-1])"

    becomes

    ActiveCell.FormulaR1C1 = "=XIRR(R[-" & RowCount & "]C[0]:R[-1]C[0],R[-" & RowCount & "]C[-1]:R[-1]C[-1])"

    And while you're at it skip the Selection step if it's not absolutely needed:

    If Cells(kRow, "A").Value = "IRR" Then 
    Cells(kRow, "A").Offset(0, 5).FormulaR1C1 = "=XIRR(R[-RowCount]C[0]:R[-1]C[0],R[-RowCount]C[-1]:R[-1]C[-1])"
Else 
    Cells(kRow, "A").Offset(0, 5)=RowCount
End If