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

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

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.


"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

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

I love this thanks.

"marcus" wrote:

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