VBA SUMIF formula not calculating result properly

Question

I have some VBA code which goes through a couple of thousand lines of data, performs a SUMIF and then supposedly performs a "Calculate Now". It runs fine in the VBA, my problem is that in the output, the format is coming up as "General" and no matter how many times i manually click "Calculate Now" it still shows 0. I have to go into the cell and then click out of it for it to realise that it is a number/formula and behave that way. Then when i F9 the sheet, it recalcs just fine.

Any ideas why that happens?

The VBA code I am using is below:


Sub MySub()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim WorkbookPathName As String
Dim wb As Workbook
Dim StrDate As String

StrDate = Format(Range("G6"), "dd-mm-yyyy")

Set wb = Workbooks.Add

WorkbookPathName = "MyPath" & StrDate

wb.SaveAs Filename:=WorkbookPathName, _
FileFormat:=51

wb.Activate

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

Const myDir As String = "MyDir"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
    
    Set FileSysL = New FileSystemObject
    Set myFolderL = FileSys.GetFolder(myDir)
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = Workbooks("Myworkbook.xlsm").Sheets("Start").Range("G6").Value
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile

    Workbooks.Open myDir & "\" & strFilename, ReadOnly:=True
    Workbooks(strFilename).Activate
 
 Range("A1:CA1").AutoFilter Field:=32, Criteria1:=Array( _
        "filter for some text which is irrelevant to the point here"), Operator:=xlFilterValues

Range("A1:CA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

wb.Activate
Range("A1").Select
ActiveSheet.Paste

Workbooks(strFilename).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close

wb.Activate
Sheets("Sheet1").Name = "Data"

Application.Calculation = xlCalculationAutomatic

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Country"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",LEFT(RC[28],2))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "lvl"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""", CONCATENATE(RC[29]&RC[41]))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Lvl Net"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[1],RC[1],C[13])/COUNTIF(C[1],RC[1])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Final"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,""Buy"",""Sell"")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)

Const sName As String = "Summary" ' Source Worksheet Name
Const cName As String = "Data" ' Criteria Worksheet Name
Const dColTitle As String = "My Column"
Const dCol As String = "A"

Dim dws As Worksheet: Set dws = ActiveSheet

dws.Columns(dCol).Insert xlToRight, xlFormatFromLeftOrAbove
    
With dws.Columns(dCol)
        .Cells(1).Value = dColTitle
        .Cells(2).Resize(LastRow - 1).FormulaR1C1 = _
            "=SUMIF('" & sName & "'!C[19],'" & cName & "'!RC[32],'" _
            & sName & "'!C)"
    End With

Side notes: the `+` in your formula is redundant, and you should probably have `0`, not `""0""`. The latter is text, not a number. — BigBen, Aug 02 '21 at 14:24
All the `*IF(S)` formula are calculation heavy and too many causes them to short circuit and return just `0`. — Scott Craner, Aug 02 '21 at 14:24
@BigBen, you *might* need to use `IFERROR` - no way to tell from the information provided, but it's quite possible for that formula to produce errors if the source data has errors in it. (whether it's a good idea is another question...) — Rory, Aug 02 '21 at 14:28
@Rory - yeah, I had a "probably" in my comment which I then deleted. — BigBen, Aug 02 '21 at 14:29
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Aug 02 '21 at 14:30
Also it's faster to get rid of all `.select selection` parts in your code. `Range("A1").Select ActiveCell.FormulaR1C1 = "My Column"` for instance can be narrowed down to `Range("A1").FormulaR1C1 = "My Column"` or `Range("A1").value = "My Column"` — P.b, Aug 02 '21 at 14:34
If the sheet you write that formula in is `Summary` then `'Summary'!C` refers to the column you write the formula in. So the result of that formula is in the sum itself. You have a circle reference here I guess. • Can you clarify in which sheet you write that formula? — Pᴇʜ, Aug 02 '21 at 14:39
hi all - thanks for the comments. I have removed the quote marks from the 0, the +, and the ISERROR wrapper. The initial result i now get is #VALUE!, again, if i double-click in and then out of the cell it works fine, but still not initially... @Pᴇʜ - i am in a sheet called Data, I need my SumIf to check if cell RC[32] in the Data sheet is in cell C[19] in the Summary sheet, and if so, sum up column 1 or [C] in the Summary tab — st87_top, Aug 02 '21 at 15:20
So `'Summary'!C[19]` refers to column `T` and `'Data'!RC[32]` refers to `AG2` and `'Summary'!C` to column A. Is that what you expect? If yes, your formula is correct and there is something odd in your data. — Pᴇʜ, Aug 02 '21 at 15:34
@Pᴇʜ that is correct. It's weird, i am using similar code for other columns I am adding and then doing a vlookup or If statement and they seem to work fine, but it is this one which is giving me a formatting problem... — st87_top, Aug 02 '21 at 16:30

score 1 · Answer 1 · answered Aug 02 '21 at 15:21

Insert Column and Write Formula

Both solutions do the same.
If you're writing to Data, use the ??? and ? lines.

Option Explicit

Sub InsertAndWrite()
    
    Dim LastRow As Long: LastRow = 10 ' e.g.
    
    Dim dws As Worksheet: Set dws = ActiveSheet
    'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code ?
    'Dim dws As Worksheet: Set dws = wb.Worksheets("Data") '???
    
    With dws.Columns("A")
        If .Cells(1).Value = "My Column" Then
            .Delete
        End If
    End With
    
    dws.Columns("A").Insert xlToRight, xlFormatFromLeftOrAbove
    
    With dws.Columns("A")
        .Cells(1).Value = "My Column"
        .Cells(2).Resize(LastRow - 1).FormulaR1C1 = _
            "=SUMIF('Summary'!C[19],'Data'!RC[32],'Summary'!C)"
    End With

End Sub


Sub InsertAndWriteUsingConstants()
    
    Const sName As String = "Summary" ' Source Worksheet Name
    Const cName As String = "Data" ' Criteria Worksheet Name
    'Const dName As String = "Data" ' Destination Worksheet Name '???
    
    Const dColTitle As String = "My Column"
    Const dCol As String = "A"
    
    Dim LastRow As Long: LastRow = 10 ' e.g.
    
    Dim dws As Worksheet: Set dws = ActiveSheet
    'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code ?
    'Dim dws As Worksheet: Set dws = wb.Worksheets(dName) '???
    
    With dws.Columns(dCol)
        If .Cells(1).Value = dColTitle Then
            .Delete
        End If
    End With
    
    dws.Columns(dCol).Insert xlToRight, xlFormatFromLeftOrAbove
    
    With dws.Columns(dCol)
        .Cells(1).Value = dColTitle
        .Cells(2).Resize(LastRow - 1).FormulaR1C1 = _
            "=SUMIF('" & sName & "'!C[19],'" & cName & "'!RC[32],'" _
            & sName & "'!C)"
    End With

End Sub

thanks - the above code is more efficient, so i will implement going fwd, but i am still left with the same problem, i.e. the result of the VBA is initially displayed as #VALUE! and I have to double click in the column and out again for it to realise it is a number and then it is displayed properly. — st87_top, Aug 02 '21 at 16:29
You may have disabled calculation. Try `Application.Calculation = xlCalculationAutomatic`. Hard to tell without seeing the preceding code. — VBasic2008, Aug 02 '21 at 16:50
ok thanks, let me update my original question with the full code. i need to edit it as it has some proprietory information. BTW when i added the Auto Calc it still didnt fix it. — st87_top, Aug 02 '21 at 17:00

VBA SUMIF formula not calculating result properly

1 Answers1

Insert Column and Write Formula