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]

 

[Phitur]

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.

There are about 2000 cells performing calculations. Of those at least half are using arrays. Once the sheet is setup, there is no need to return to it to manipulate the data. This code is intended to handle the analysis for final use. If the data does need to be referenced, it is still preserved in a usable format.

Per Fazza's comments, I think that would be a good approach to pursue (or an actual DB) for the next project. Something I'd need to research.

I converted the equation you suggested, but I get a type mismatch error. Here is the conversion I used.

Cells(i, col12).Value = Application.SumIf(Range(col1 & Chr(58) & col1), "<" & Filter(i + 1) _
  , "G:G") - Application.SumIf(Range(col1 & Chr(58) & col1), "<" & Filter(i), "G:G")

What would be causing the error?

Thanks

 

[mikerickson]

If all the string variables resolve properly (e.g. col1 & Chr(58) & col1) the only thing that I can see is the "G:G" is not a range, but Range("G:G") is.

Try this syntax,
Application.SumIf(Range(col1 & Chr(58) & col1), "<" & Filter(i + 1), Range("G:G"))

 

[Phitur]

If all the string variables resolve properly (e.g. col1 & Chr(58) & col1) the only thing that I can see is the "G:G" is not a range, but Range("G:G") is.

Try this syntax,
Application.SumIf(Range(col1 & Chr(58) & col1), "<" & Filter(i + 1), Range("G:G"))

That's it! So many simple things. Thanks for your help!