Incriment veriable in a VBA formula

ThaiLove

New Member
Joined
Nov 7, 2012
Messages
10
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
Joined
Aug 13, 2011
Messages
2,932
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:
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,638
Members
410,696
Latest member
JTrehan
Top