=SUMIF in VBA

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
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:

Code:
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
Hi, you need to add in the quotes. For VBA, these will be double quotes.
Code:
Range("H35").Formula = "=sumif(H4:H32,""<=""&0)"
 
Upvote 0
Hi

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

Code:
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
Code:
[LEFT][COLOR=#333333][FONT=monospace]=sumif(H4:H32,<=0")[/FONT][/COLOR][/LEFT]

Macro with R1C1 is

Code:
Range("H35").FormulaR1C1 = "=SUMIF(R[-31]C:R[-3]C,""<=0"")"
 
Last edited:
Upvote 0
Instead of a formulae, I would rather use
Code:
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
 
Upvote 0
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!

Code:
    Range("H35").Formula = "=sumif(H4:H32,""<=""&0)"
Komolga - unfortunately I have an identical result with your suggestion:

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

But, at least the macro is running now!
 
Upvote 0
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?
 
Upvote 0
Instead of a formulae, I would rather use
Code:
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?
 
Upvote 0
Hi

this is the code I am presently working with:

Code:
    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.
 
Upvote 0
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:

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

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top