How to speed up this macro?

[Bernie Deitrick]

Ctech,

This code:

Range("A2").FormulaR1C1 = _
"=IF(SUMIF(C[9],RC[9],C[10])=0,""SortLow"",""SortHigh"")"

C[9] means 9 columns to the right of the current column (A), so C[9] = J:J, C[10] = K:K

Creates this formula:

=IF(SUMIF(J:J,J2,K:K)=0,"SortLow","SortHigh")

This part

SUMIF(J:J,J2,K:K)

returns the sum from column K (Your initial column J) where the value in column J (your initial
column I) is the same as that of the current row.

Lets say that column J and K has these values

Bernie 5
Ctech 4
Fred 0
Bernie 6
Ctech 4
Fred 0

The SUMIF would return values like this:

11 Bernie 5
8 Ctech 4
0 Fred 0
11 Bernie 6
8 Ctech 4
0 Fred 0

But the IF function would change those to

SortHigh Bernie 5
SortHigh Ctech 4
SortLow Fred 0
SortHigh Bernie 6
SortHigh Ctech 4
SortLow Fred 0

Which, when sorted, would be

SortHigh Bernie 5
SortHigh Ctech 4
SortHigh Bernie 6
SortHigh Ctech 4
SortLow Fred 0
SortLow Fred 0

Then the bottom two rows would be deleted.

HTH,
Bernie
MS Excel MVP

This helped a lot, however I don't understand how this code

Range("A2").FormulaR1C1 = _
"=IF(SUMIF(C[9],RC[9],C[10])=0,""SortLow"",""SortHigh"")"

works

I guess:
C=[9] = Column 9 from A ("I:I" - Cost center column)
RC[9] = Cell 9 from A ("I?" - Cost center)
C[10] = Column 10 from A ("J:J" - Supplier column)

How does this Sum column K (Func_Value coulmn)?

Could you explain this, even more? I have read the SUMIF help on Excel,
however it didn't help much.


Thanks so far for all the help.