VBA Evaluate Array Formula

[Leila33]

Hello,

I'm trying to use the following code to evaluate an array formula across a range of cells:

Sub EvaluateTest()


ActiveSheet.Range("D2:G2").Value = Evaluate("=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"""")")

ActiveSheet.Range("D2:G10000").FillDown


End Sub


However, I keep getting the result from the 1st cell copied across the entire range. The desired result should be different for each cell as the row and column change.

When I insert the formula manually and copy it down Excel, it works. However, I have some code on the other sheet that must be run daily. Such code does not work/freezes when links are present on this sheet, since the links are literally updating every second it runs.

Thus, this is why I need evaluated results only. Does anyone know how to fix this?? Thanks in advance.

Ps: I'm not very fluent in VBA. Sorry for any inconvenience.

 

[Leila33]

I am basically trying to convert the code above into a loop, in such way that "A2" becomes A3, and then A4, A5, and so on as you go down each row...I have tried:

Dim i As Integer
For i = 2 To 10
Cells(i, 4).Value = Evaluate("=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$100000* ​ 0=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFile* ​s!Ai))),"""")")
Next i
End Sub


Here I am only trying to change the rows; ideally, both row and columns would change, but I can deal with having to use a different formula per column.

 

[StephenCrump]

I think this is what you're looking to do:

With Range("D2:D20")
    .Formula = "=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"""")"
    .FormulaArray = .FormulaR1C1
    .Value = .Value
End With

I wouldn't try this on too large a range, or else the worksheet is going to take a long time to calculate.

 

[Leila33]

wowow this works perfectly, many thanks

 

[StephenCrump]

You're welcome, I'm glad it worked.

 

[Leila33]

I have modified the code because I now need two different formulas, for different ranges:

Sub TagSearch()

With Range("D2:E50")
.Formula = “=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF((AllFiles!$C$2:$C$1000000=$A$2)*( AllFiles!$A$2:$A$1000000>=$B$2)*( AllFiles!$A$2:$A$1000000<=$C$2),ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),""””)”
.FormulaArray = .FormulaR1C1
.Value = .Value
End With

With Range("F2:AR50")
.Formula = “=IFERROR(INDEX(AllFiles!$D$2:$D$1000000,SMALL(IF((AllFiles!$C$2:$C$1000000=F$1)*( AllFiles!$A$2:$A$1000000>=$B$2)*( AllFiles!$A$2:$A$1000000<=$C$2),ROW(AllFiles!$D$2:$D$1000000)-ROW(AllFiles!$D$2)+1),ROWS(AllFiles!$D$2:AllFiles!$D2))),""””)”
.FormulaArray = .FormulaR1C1
.Value = .Value
End With


End Sub


However, I now get the error #13 for type mismatch, at the .FormulaArray line...maybe because it's being redefined? I thought that with different with statements it should be fine, but I guess not...any ideas? Thanks

I also added some extra conditions I needed, but I don't think that has anything to do with it. The conditions work fine when one formula is used. The fact that there are 2 of them seems to be the problem..

 

[StephenCrump]

I can replicate the error. It looks like it's an Excel resources issue because your formulae are working with a million rows.

If I reduce the number of rows to, say, 100, then this code works just fine:

With Range("D2:E50")
    .Formula = "=IFERROR(INDEX(AllFiles!A$2:A$100,SMALL(IF((AllFiles!$C$2:$C$100=$A$2)*( AllFiles!$A$2:$A$100>=$B$2)*( AllFiles!$A$2:$A$100<=$C$2),ROW(AllFiles!A$2:A$100)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"""")"
    .FormulaArray = .FormulaR1C1
    .Value = .Value
End With

Do you really need to be working with a million rows. If so, a change of approach might be needed, e.g. perhaps take a copy of AllFiles!A:C, paste the values somewhere, and just filter or sort the values you actually need?

 

[Leila33]

Hello,

Unfortunately, I do need the million rows. The data on the sheet "All Files" will have approximately that many rows eventually as it's updated. It'll have a year's worth of data.

I'm pulling 50 rows on the formula for testing, but it's actually around 5000. The thing is, the formula is pulling values from a bunch of different categories on the big sheet, and setting them up side by side. It's slow, but the other methods I tried (such as setting up links) were even slower.

I need two formulas because of two different criteria patterns...I guess I can use separate macros for each formula, but I just wanted to see if they could work in one. Thanks.