I have 2 pieces of code, the first piece works:
Set rg = Range("Test!G:G")
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

and the 2nd doesn't work, it fails on the last statement:
With Worksheets("Test")
varCol = 7
Set rg = .Range(.Cells(2, varCol), .Cells(2, varCol).End(xlDown))
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

What am I doing wrong ? I cannot use the "G:G" syntax because the column I
need to sum is held in a variable (here varCol)

I'm fairly new at Excel programming, so don't hesitate to state the obvious.

Sumif takes 2 arrays as the first 2 arguments. Those arrays MUST be the same
size. In your second example your arrays aren't the same size. Make rg the
entire column something like this...

With Worksheets("Test")
varCol = 7
Set rg = .columns(varCol)
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

--
HTH...

Jim Thomlinson


"AlanKohl" wrote:

I have 2 pieces of code, the first piece works:
Set rg = Range("Test!G:G")
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

and the 2nd doesn't work, it fails on the last statement:
With Worksheets("Test")
varCol = 7
Set rg = .Range(.Cells(2, varCol), .Cells(2, varCol).End(xlDown))
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

What am I doing wrong ? I cannot use the "G:G" syntax because the column I
need to sum is held in a variable (here varCol)

I'm fairly new at Excel programming, so don't hesitate to state the obvious.

Looks like you have a type AFTER sumIf
--
http://HelpExcel.com




"AlanKohl" wrote:

I have 2 pieces of code, the first piece works:
Set rg = Range("Test!G:G")
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

and the 2nd doesn't work, it fails on the last statement:
With Worksheets("Test")
varCol = 7
Set rg = .Range(.Cells(2, varCol), .Cells(2, varCol).End(xlDown))
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

What am I doing wrong ? I cannot use the "G:G" syntax because the column I
need to sum is held in a variable (here varCol)

I'm fairly new at Excel programming, so don't hesitate to state the obvious.

Thanks Jim, that solved the problem.

"Jim Thomlinson" wrote:

Sumif takes 2 arrays as the first 2 arguments. Those arrays MUST be the same
size. In your second example your arrays aren't the same size. Make rg the
entire column something like this...

With Worksheets("Test")
varCol = 7
Set rg = .columns(varCol)
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

--
HTH...

Jim Thomlinson


"AlanKohl" wrote:

I have 2 pieces of code, the first piece works:
Set rg = Range("Test!G:G")
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

and the 2nd doesn't work, it fails on the last statement:
With Worksheets("Test")
varCol = 7
Set rg = .Range(.Cells(2, varCol), .Cells(2, varCol).End(xlDown))
End With
F = Application.WorksheetFunction.SumIfs(rg, Range("Test!C:C"), "<1")

What am I doing wrong ? I cannot use the "G:G" syntax because the column I
need to sum is held in a variable (here varCol)

I'm fairly new at Excel programming, so don't hesitate to state the obvious.