=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.

 

[RoryA]

This should be enough:

    With Sheets("Pivot")
        Dim SumRange As Range
        Set SumRange = .Range("H2:H34")
        .Range("H35").Value2 = Application.WorksheetFunction.SumIf(SumRange, "<0")
        .Range("H35").Value2 = Application.WorksheetFunction.SumIf(SumRange, ">0")
    End With

 

[Small Paul]

Hi All

Sorted, thanks to you all. I have simply combined the 2 code styles!

    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
    
Range("H37").Formula = "=sumif(H4:H32,"">""&0)"

Many thanks for your help and patience.
Small Paul.

 

[steve the fish]

Oh ok. You are now going to have a fixed value in cell H35 and a working formula in H37. Thats unlikely to be what is required unless the data is static forever.

 

[Small Paul]

Hi steve the fish

It is a spreadsheet produced from Salesforce (and macro produces the pivot and selected totals) each morning so it should be fine.

Many thanks
Small Paul.

 

[Kamolga]

For what reason?

I rather put formulae myself on sheet and if I move to VBA, then it will put the values whenever needed to save space and much easier to retrieve. In addition formulae in VBA are not cristal clear to me, when need to come back. Rather define a UDF with easy names / named range or put values.

As I understand your concept, you recieve daily figures from sales and would like to avoid redoing a pivot every time.

My approach, which is certainly not the best, would be to have a table. I would import data in this table using a macro (for example on Worbook_open event. Once you have a clean table with right colums and proper format, sky is the limit. As table is dynamic (rows expend automatically and formulae come alone on each row), the pivot only needs refresh after a data update, wich I always do automatically on the pivot worksheet.activate event so I can forget about it.

Note: the issue you had with H35 being empty means you did not activate the right sheet before the code line or there were no negative numbers and you do not show zero value on that sheet

 

[Small Paul]

Hi Kamolga

Yes, I realised my error of not activating the sheet quite late on. But, to be fair to my stupid brain, it did get there!!

With regards the rest of your post, I do not understand the bulk of what you say. I am going to have to read it quite a few times (and then do some searching) before I get it.

Thank you for your help.
Small Paul.

 

[Kamolga]

With regards the rest of your post, I do not understand the bulk of what you say. I am going to have to read it quite a few times (and then do some searching) before I get it.

I will try to save you some time:
Table
If your data is 50 rows today and you make a pivot, you will have to redo it again if you add 5 lines. If those 50 lines are in a table (insert tab/table) and you do a pivot from there, your pivot will include the 5 rows you add any other time.
Worbook open event
You can get get a macro to do something when you open a workbook (it can import data for example). To do this, when you are in VBA, 2x click This Workbook (in left column below all the sheets) and paste

Private Sub Workbook_Open()

'Write or call a macro


End Sub

Refresh Pivot

Pivots need to be refreshed every time the data change (like the 5 rows we added in previous example). Because I always put my pivot on another sheet than its data, it is convenient for me to refresh it automatically when I come on its sheet, so in user view it is always updated. To do so, right-click the sheet where the pivot is, click vew code and paste

Private Sub Worksheet_Activate()ActiveWorkbook.RefreshAll
End Sub

Note: this updates all pivots of the workbook, it is suitable as long as the file is not too big, otherwise finetuning necessary

 

[Small Paul]

Hi Kamolga
Wow, I cannot thank you enough for the explanation. Reading that in conjunction with your post, it certainly makes logical sense.
I too always have pivots (often multiple) on a separate sheet to the data so it is easy to produce a meaningful report for my bosses.
Much of my work downloads from Salesforce and is automatically in table format. However, I have a few very large worksheets, which are added to daily, that are simply data. I shall be changing those today for sure!
They say 'you can't teach an old dog new tricks'! I am very new to VBA but thanks to contributors on this site I am learning fast!
I truly appreciate your help and, in particular, going the extra mile with the explanation.
Cheers
Small Paul.