Sumif Macro

[Rick]

I process a lot of jnls that are uploaded into our system from Excel. As such
to make sure they balance I put 2 Sumif formulas at the bottom of the column,
both should agree to each other.

I am sick of having to write these each time and so I want to automate it.

The situations is this.

The top of the range is row 2 but the bottom varies with the number of
transactions. The formula will be at the bottom of the column to be added and
the reference column is 2 columns to the left.

A typical set of formulas would look like this.

In Cell H791 - =sumif($F$2:$F$789,40,$H$2:$H$789)
In Cell H792 - =sumif($F$2:$F$789,50,$H$2:$H$789)

Occasionally the 40 or 50 will be something different but I can amend that
once the formula is written.

How can I write a macro to duplicate this so that the top cell is in Row 2
and the bottom cell is 2 cells above where the active cell is and where the
formulas are to be?

Thanks in advance.
Rick

 

[marcus]

Hi Rick

This should do the trick.

Regards

Marcus

Sub CreateSum()

Dim Lw As Integer, Sr As Integer

Lw = Range("G" & Rows.Count).End(xlUp).Row + 2
Sr = Lw - 1 'for the Sum row
Range("H" & Lw).Value = "=Sumif(F2:F" & Sr & ",40,H2:H" & Sr &
")"
Lw = Lw + 1
Range("H" & Lw).Value = "=Sumif(F2:F" & Sr & ",50,H2:H" & Sr &
")"

End Sub

 

[Christopher Panadol]

Hope this help:

Option Explicit
Sub Main()

Dim xRow, yRow, xClause

xRow = 2
Do While Not IsEmpty(Cells(xRow,1).Value) ' Assume each cell of your
column 1 is not empty
xRow = xRow + 1
Loop

yRow = xRow + 2 ' Shift two rows to enter your formula
Cells(yRow, 8).FormulaR1C1 = "=SUMIF(R[-" & Trim(Str(xRow)) &
"]C[-2]:R[-3]C[-2], 40, R[-" & _
Trim(Str(xRow)) & "]C:R[-3]C)"

End Sub

The advantage of using R1C1 format is that it uses the fixed clause to
locate your needed cell dynamically. It seems troublesome but it works.

 

[Christopher Panadol]

Option Explicit
Sub Main()

Dim xRow, yRow, xClause

xRow = 2
Do While Not IsEmpty(Cells(xRow,1).Value) ' Assume each cell of your
column 1 is not empty
xRow = xRow + 1
Loop

yRow = xRow + 2 ' Shift two rows to enter your formula
Cells(yRow, 8).FormulaR1C1 = "=SUMIF(R[-" & Trim(Str(xRow)) & _
"]C[-2]:R[-3]C[-2], 40, R[-" & Trim(Str(xRow)) & "]C:R[-3]C)"

Cells(yRow + 1, 8).FormulaR1C1 = "=SUMIF(R[-" & Trim(Str(xRow + 1)) & _
"]C[-2]:R[-4]C[-2], 40, R[-" & Trim(Str(xRow + 1)) & "]C:R[-4]C)"

End Sub

 

[Rick]

I love this thanks.