# Incriment veriable in a VBA formula

#### ThaiLove

##### New Member
Hi Guys,

I've been working on this for a while, I feel like I'm close, but I'm not playing horse shoes... I wouldn't bother with VBA, but Excel can't seem to be able to pick up the pattern

My goal is to get the following formulas in to cells I3 through I100

I3 should have: =INDEX(J\$2:J\$2,MAX(IF((C\$2:C\$2=C\$3),ROW(J\$2:J\$2)-1)))
I4 should have: =INDEX(J\$2:J\$3,MAX(IF((C\$2:C\$3=C\$4),ROW(J\$2:J\$3)-1)))
I5 should have: =INDEX(J\$2:J\$4,MAX(IF((C\$2:C\$4=C\$5),ROW(J\$2:J\$4)-1)))
I6 should have: =INDEX(J\$2:J\$5,MAX(IF((C\$2:C\$5=C\$6),ROW(J\$2:J\$5)-1)))
...
I100 should have =INDEX(J\$2:J\$99,MAX(IF((C\$2:C\$99=C\$100),ROW(J\$2:J\$99)-1)))

Thanks a bunch,

ThaiLove

Sub FindPrevious()
Dim LastV As Byte
Dim CurV As Byte

LastV = 2
CurV = 3
For Row = 3 To 100

wanted = "=INDEX(J\$2:J\$LastV,MAX(IF((C\$2:C\$LastV=C\$CurV),ROW(J\$2:J\$LastV)-1)))"

Cells(Row, "I").FormulaR1C1 = wanted
LastV + 1
CurV + 1
Next Row
End Sub

#### circledchicken

##### Well-known Member
Thanks! dead on, works perfectly, the only thing I wonder is if there's a way for it to give me the output right away? When I add your code, the proper formula is there, the only thing is I have to get into the formula & click "Ctrl + Shift + Enter" to get the brackets "{}" around the formula & give me the result I want. Is there a bypass for this?

Thanks a lot TL
Try:

Code:
``````ActiveCell.Offset(0, 8)[SIZE=3][COLOR=#800000][B].FormulaArray[/B][/COLOR][/SIZE] = _
"=INDEX(J\$2:J" & Rw - 1 & ",MAX(IF((C\$2:C" & Rw - 1 & "=C" & Rw & "),ROW(J\$2:J" & Rw - 1 & ")-1)))"``````

For a detailed look at the FormulaArray property see this very useful article:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### ThaiLove

##### New Member
I'm impressed Replies
0
Views
36
Replies
4
Views
53
Replies
9
Views
66
Replies
3
Views
192
Replies
1
Views
104