=SUMIF in VBA

[Small Paul]

Hi

I am sure this is very simple but having tried a variety of options from other threads I cannot solve it.

I am trying to add a simple =sumif formula to a macro:

Range("H35").Select
    .FormulaR1C1 = "=sumif(H4:H32,<=0)"

I have tried without the R1C1 as well but the error 'invalid or unqualified reference' is given, no matter what I try.

Any assistance would be appreciated.

Many thanks
Small Paul.

 

[steve the fish]

You cant use column letter references when using R1C1 as you have identified so get rid of the R1C1. The unqualified reference refers to your dot (.Formula etc) without any associated With statement so use Range("H35").Formula etc in one line.

 

[BarryL]

Hi, you need to add in the quotes. For VBA, these will be double quotes.

Range("H35").Formula = "=sumif(H4:H32,""<=""&0)"

 

[Kamolga]

Hi

I am trying to add a simple =sumif formula to a macro:

Range("H35").Select
    .FormulaR1C1 = "=sumif(H4:H32,<=0)"

I have tried without the R1C1 as well but the error 'invalid or unqualified reference' is given, no matter what I try.

When you want to do a sumif <=0, you should put it in "". In a cell we would write

[LEFT][COLOR=#333333][FONT=monospace]=sumif(H4:H32,<=0")[/FONT][/COLOR][/LEFT]

Macro with R1C1 is

Range("H35").FormulaR1C1 = "=SUMIF(R[-31]C:R[-3]C,""<=0"")"

 

[Kamolga]

Instead of a formulae, I would rather use

Dim rCell As Range
Dim SumRange As Range
Dim r As Long
r = 0
Set SumRange = Range("H2:H34")
For Each rCell In SumRange
'
    If rCell < 0 Then
      r = r + rCell
    End If
Next rCell
Range("H35") = r

 

[Small Paul]

Hi
Many thanks for your fast response.
BarryL - I have tried your solution (see below) and whilst no 'error' is presented (so the macro runs to the end), cell H35 remains empty!

    Range("H35").Formula = "=sumif(H4:H32,""<=""&0)"

Komolga - unfortunately I have an identical result with your suggestion:

    Range("H35").FormulaR1C1 = "=SUMIF(R[-31]C:R[-3]C,""<=0"")"

But, at least the macro is running now!

 

[BarryL]

Hi Small Paul,

I ran that piece of code and it populated the cell with the desired formula.

Any chance it is populating a different worksheet? Is the cell just empty?

Can you post the rest of your code?

 

[steve the fish]

Instead of a formulae, I would rather use

Dim rCell As Range
Dim SumRange As Range
Dim r As Long
r = 0
Set SumRange = Range("H2:H34")
For Each rCell In SumRange
'
    If rCell < 0 Then
      r = r + rCell
    End If
Next rCell
Range("H35") = r

For what reason?

 

[Small Paul]

Hi

this is the code I am presently working with:

    Sheets("Pivot").Select    Dim rCell As Range
    Dim SumRange As Range
    Dim r As Long
    r = 0
    Set SumRange = Range("H2:H34")
    For Each rCell In SumRange
    '
    If rCell < 0 Then
      r = r + rCell
    End If
Next rCell
Range("H35") = r
    
    Set SumRange = Range("H2:H34")
    For Each rCell In SumRange
    '
    If rCell > 0 Then
      r = r + rCell
    End If
Next rCell
Range("H37") = r

Cell H35 does now total everything <0
I am trying to workout cell H37 (total >0). Is the 'r' a definitive letter or can it be any letter to differentiate this from previous section?

Small Paul.

 

[steve the fish]

Ok but you asked for a formula to be placed in a cell. What you have there doesnt do that. If what you have there works then this does:

With Sheets("Pivot")
    .Range("H35").Formula = "=SUMIF(H4:H32,""<=0"")"
    .Range("H37").Formula = "=SUMIF(H4:H32,"">0"")"
End With