Formula with dynamic cell reference

Question

I have the following formula with a S1 cell reference which increments as the formula is executed across the range, e.g. S2,S3,S4.

=INDEX(Range1,((ROW(S1)-1)*Range1=2+ROW(S1)),1,MATCH(1,(Range3=Fixed_Period)*(Range4=Mortgage_InitialRate),0))

I use this snippet of code to apply aforementioned formula to every cell in a dynamic range, however, since S1 doesn't increment via such approach, the result for every cell is identical. Thus, how can I make sure S1 increments with every new row to S2, S3, S4 and etc.

Sub Formula()
Dim FLrange As Range
Set FLrange = Range("Range10")

For Each cell In FLrange
cell.Offset(0, 5).FormulaArray = "=INDEX(Range1,((ROW(S1)-1)*Range1=2+ROW(S1)),1,MATCH(1,(Range3=Fixed_Period)*(Range4=Mortgage_InitialRate),0))"

Next cell
End Sub

Besides, ((ROW(S1)-1)*Range1=2+ROW(S1)), you're indexing by a Boolean value? Seems there's a syntax error in your formula. — A.S.H

Amorpheuses Amorpheuses · Accepted Answer · 2017-02-17T05:08:23

Increment an integer variable (say 'num') for each loop iteration and use "S" & num instead of "S1"

Sub Formula()
  Dim FLrange As Range
  Set FLrange = Range("Range10")
  Dim num As Integer
  num=1
  For Each cell In FLrange
    cell.Offset(0, 5).FormulaArray = "=INDEX(Range1,((ROW(S" & num & ")-1)*Range1=2+ROW(S" & num & ")),1,MATCH(1,(Range3=Fixed_Period)*(Range4=Mortgage_InitialRate),0))"
    num=num+1
  Next cell
End Sub

Formula with dynamic cell reference

1 Answers