Convert Excel SUM IF formula array to VBA code

[Phitur]

I need to pare down the number of calculations on the worksheet because it is slow. I'm trying to convert the following formula to a VBA code.

{=SUM(IF(I:I>=AI3,IF(I:I<AI4,1,0),0))}

where,
col1 = "I"
col8 = "AI"
col11 = "AL"
i = 3
the current cell = AL3


Does anyone know how this could be represented in VBA to fill in the value for that cell instead of putting the equation in? I'm a bit lost on this one.

Thanks!


Reposting from http://www.mrexcel.com/forum/showthread.php?t=495161
[/COLOR]

 

[Jonmo1]

Well, number one...Just FYI..

VBA Does Not mean "Faster"
It still has to do the same math and calculations.

The only advantage you get by doing it in vba is the ability to control when and how frequently it gets recalculated.
But the calculation itself is NOT any faster.
In fact, it's quite often actually slower.

But anyway, you could do it like this



Range("A1").FormulaArray = "=SUM(IF(I:I > =AI3,IF(I:I < AI4,1,0),0))"
Range("A1").Value = Range("A1").Value

 

[Jonmo1]

Just a continuation on the "vba is not faster" bit...


The best advise to improve performance of that formula is to not use entire column refs like I:I

Try


=SUM(IF(I1:I1000 > =AI3,IF(I1:I1000 < AI4,1,0),0))
That will greatly improve performance.

 

[Phitur]

Understand it won't be faster. The sheet is so large that any changes to it directly take forever due to all of the formulaarrays.

I'm currently entering the equation in the cell with this code in VBA

Range(col7 & "3").FormulaArray = "=SUM(IF(" & col1 & _
  Chr(58) & col1 & ">=" & col6 & "3,IF(" & col1 & Chr(58) & col1 & _
  "<" & col6 & "4,1,0),0))"

I'd rather have the macro put only the resulting value into the cell so the sheet does no further calculations.

 

[Jonmo1]

Turn off Calculation when you put in the formula
And do the range.value = range.value to remove the formula

Application.Calculation = xlcalculationmanual
Range(col7 & "3").FormulaArray = "=SUM(IF(" & col1 & _
  Chr(58) & col1 & ">=" & col6 & "3,IF(" & col1 & Chr(58) & col1 & _
  "<" & col6 & "4,1,0),0))"
Range(col7 & "3").Value = Range(col7 & "3").Value
Application.Calculation = xlcalculationautomatic

 

[Phitur]

Turn off Calculation when you put in the formula
And do the range.value = range.value to remove the formula

Application.Calculation = xlcalculationmanual
Range(col7 & "3").FormulaArray = "=SUM(IF(" & col1 & _
  Chr(58) & col1 & ">=" & col6 & "3,IF(" & col1 & Chr(58) & col1 & _
  "<" & col6 & "4,1,0),0))"
Range(col7 & "3").Value = Range(col7 & "3").Value
Application.Calculation = xlcalculationautomatic

The next step is that I use

Range(col7 & "3:" & col9 & "3").AutoFill Destination:= _
  Range(col7 & "3:" & col9 & GroupLastRow)

I'm guessing I would just run a loop with

for i = 3 to lastrow
  Application.Calculation = xlcalculationmanual
  Range(col7 & i).Value = Range(col7 & i).Value
  Application.Calculation = xlcalculationautomatic
next i

to change the equation in each row to final values only?

Right now I'm working on your suggestion to narrow the rows down to only those used instead of the entire column.

Thanks for your help!

 

[mikerickson]

It looks like that spreadsheet formula could be replaced with the non-Array formula,
=COUNTIF(I:I, "<"&A14)-COUNTIF(I:I, "<"&A13)

Putting it into VBA would give

Dim lowEndCondition as String, highEndCondition as String

lowEndCondition = "<"&Range("A13")
highEndCondition = "<"&Range("A14")

MsgBox Application.CountIf(Range("I:I"), highEndCondition) - Application.CountIf(Range("I:I"), lowEndContidion)

 

[Phitur]

Same problem with sumproduct. Trying to get this excel equation into VBA

=SUMPRODUCT((I:I>=AI3)*(I:I<AI4),G:G)

<?XML:NAMESPACE PREFIX = AI5),G /><AI5),G:G)[ p PHP]<><?XML:NAMESPACE PREFIX = AI4),G /><AI4),G:G)[ p PHP]<>This is what I have but it gives me a type mismatch error</AI4),G:G)[>
<AI4),G:G)[ p PHP]<>

Cells(i, "AM").Value = Application.SumProduct((Range("I2:I" _
  & LastRow) & ">=" & Filter(i)) * Range("I2:I" & LastRow) & _
  "<" & Filter(i + 1), "G:G")

How do I resolve this?

Thanks
</AI4),G:G)[>
</AI5),G:G)[>

 

[mikerickson]

Using SUMPRODUCT in VBA is probematic. Why not use

=SUMIF(I:I,"<"&A14,G:G)-SUMIF(I:I,"<"&A13,G:G) as your base formula and convert that.

Echoing Jonmo1's sentiments, how many cells are holding these formulas?
Its rare that substituting a UDF for sheet formulas saves time. Perhaps a Change event could be used to gain speed, but maintaining and editing the sheet in the future will be difficult, especially when the developer (you) is not super conversant in VBA.

 

[Fazza]

Suggest you use alternative approaches to avoid the array formulas entirely. They can be horribly slow on large datasets. Suggest if possible setting the data up in tables and working without any formulas, such as with query tables or pivot tables or other methods.