Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double Dim Found As Variant Dim varData Dim varVal Dim varValue Dim n As Long varData = RangeToSum.Value For n = LBound(varData, 1) To UBound(varData, 1) ' load array data If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then Found = Evaluate(varData(n, 1)) If Not IsError(Found) Then ' check for match on lookup value varVal = Application.VLookup(Lookupvalue, Found, 1, False) If Not IsError(varVal) Then ' return relvant value from second column varValue = Application.VLookup(Lookupvalue, Found, 2, False) ' make sure it's a number If IsNumeric(varValue) Then SumVlookups = SumVlookups + varValue Else SumVlookups = CVErr(xlErrValue) Exit For End If End If End If End If Next nEnd Function

Ans: ok, I get it, of course it is, it's checking the existence of lookupvalue without having to run the evaluate. ok. clever. because by definition of array constant, the comma is obligatory for a valid lookup, and it has to be much faster than running a lookup on the evaluated result in order to decide whether to run the lookup on col2, because at the least it's running one function instead of two.

I am pondering removal of the error process because it would be much faster to mask everything and then in the event the control totals are out, we can run a more sophisticated UDF to see where/if the errors are. which will be invalid input errors; of course that won't catch wrong valid input errors and I think these invalid input errors are unlikely because there is also a single total being generated from each string at time of input and that would have to be accepted while wrong... and that itself gets check against another control total and as an individual amount in its own right. Conclusion: kill the error trapping to speed up the calcs. mask everything. finally. I can use a separate evaluate to run data check on this, rather than run audit checks inside every calculation for eternity.

this is versus the original code:

Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single Dim ArrayStr As Range Dim Found As Variant For Each ArrayStr In RangeToSum Found = Evaluate(ArrayStr.Value) If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False) Next ArrayStrEnd Function

Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double Dim varRowData Dim varItemData Dim varData Dim n As Long Dim x As Long Dim y As Long Dim lngStart As Long Dim lngStop As Long varData = RangeToSum.Value For n = LBound(varData, 1) To UBound(varData, 1) ' load array data If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then varRowData = Split(Mid(varData(n, 1), 2, Len(varData(n, 1)) - 2), ";") For x = LBound(varRowData) To UBound(varRowData) varItemData = Split(varRowData(x), ",") For y = LBound(varItemData) To UBound(varItemData) Step 2 If varItemData(y) = Lookupvalue Then SumVlookups = SumVlookups + varItemData(y + 1) Exit For End If Next y Next x End If Next nEnd Function

I figured out why I thought the original was calculating faster, the screenfull in view was updating almost instantly, but the remaining approx 950 rows weren't.

So, that's back to speed testing. looking at the above now.. as best I can of course.

This one is reasonably quick, if a little illegible. ;)

Function SumByCriteria(varCriterion, rngData As Range) As Double Dim varData Dim varSubData Dim n As Long With rngData varData = Filter(.Worksheet.Evaluate("transpose(if(" & .Address & "="""","""",MID(" & .Address & ",2,len(" & .Address & ")-2)))"), varCriterion & ",") End With varSubData = Filter(Split(Join(varData, ";"), ";"), varCriterion & ",") For n = LBound(varSubData) To UBound(varSubData) SumByCriteria = SumByCriteria + CDbl(Split(varSubData(n), ",")(1)) Next nEnd Function

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

figure I could hide it away in an XLM if it doesn't slow things down, easier to maintain one complex formula than many.

0

Featured Post

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.

The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability.
Flipping Coins in Excel: Enter =RAND() into cell A2:
Recalculate the random variable…