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
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