Incriment veriable in a VBA formula

[ThaiLove]

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]

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:

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:

• Working With Range.FormulaArray In VBA | RAD Excel

 

[ThaiLove]

I'm impressed