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

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.

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top