# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

I'm impressed

Replies
4
Views
44
Replies
3
Views
90
Replies
6
Views
88
Replies
4
Views
69
Replies
12
Views
257

1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...