Sumif in VBA

[Enzo_Matrix]

I have created this code to help with our production schedule but the values are not returning what I would expect them to.

Sub ProductCount()'Create 3 headings of Overdue,Today,Next Week and sum data next to each heading


Dim OD As Range
Set OD = Sheets("Weld").Range("B1")
Dim TDY As Range
Set TDY = Sheets("Weld").Range("D1")
Dim TW As Range
Set TW = Sheets("Weld").Range("F1")


Sheets("Weld").Activate
    
    ActiveSheet.Range("A1").Select
        ActiveCell.Value = "Overdue"
    With OD
        .Formula = "=SUMIF($G:$G," > " & TODAY(),$F:$F)"
        .Value = .Value
    End With


    ActiveSheet.Range("C1").Select
        ActiveCell.Value = "Today"
    With TDY
        .Formula = "=Sumif($G:$G,Today(),$F:$F)"
        .Value = .Value
    End With
    
    ActiveSheet.Range("E1").Select
        ActiveCell.Value = "This Week"
    With TW
        .Formula = "=Sumif($G:$G," > " & TODAY(),$F:$F)"
        .Value = .Value
    End With
        
End Sub

What is supposed to happen
A1: "Overdue"
B1: count of product past due
- Actual result is the word 'True'
C1: "Today"
D1: count for product due for completion today
E1: "This Week"
F1: count of product for next week
- Actual result is the word 'True'

If I put this directly into a cell it does generate the correct value

=Sumif($G:$G," > " & TODAY(),$F:$F)

can someone please help me with my code to give actual values in cells B1, F1.

 

[sheetspread]

application.sumif() in VBA

 

[Enzo_Matrix]

Modified line of code

ActiveSheet.Range("A1").Select        
ActiveCell.Value = "Overdue"
    With OD
        .Value = Application.SumIf("F", " > " & TODAY(), "G")
    End With

I ran the code and received an error:
"Compile error:
Sub or Function not defined"

Please help

 

[sheetspread]

Use Date instead of Today()

You can't type a single letter. Try e.g. Range("F:F") but include the rows and columns rather than just single columns (which will contain many empty cells and slow down the calculation)

 

[Enzo_Matrix]

If I use Date for this instead of TODAY, is there a way to set it so that the date moves with each day of the week?

As I originally mentioned, this will be a daily production schedule that has it's fields updated with each new entry

 

[Joe4]

If I use Date for this instead of TODAY, is there a way to set it so that the date moves with each day of the week?

Date is the VBA equivalent of TODAY(). They work the same way.
The VBA equivalent of the Excel Date function is DateSerial

Confused yet?

 

[Enzo_Matrix]

beyond belief.
This is what my code now looks like and I am getting some values, but not all.
B1 should have a total of overdue orders, it shows '0'
F1 should show orders for next week, it shows '0'
D1 correctly shows orders due today at '49'

Sub Production()'set fields of count as well as hose count for each category


Dim OD As Range
Set OD = Sheets("Weld").Range("B1")
Dim TDY As Range
Set TDY = Sheets("Weld").Range("D1")
Dim TW As Range
Set TW = Sheets("Weld").Range("F1")


Sheets("Weld").Activate
    
    ActiveSheet.Range("A1").Select
        ActiveCell.Value = "Overdue"
    With OD
        Cells(1, 2) = Application.WorksheetFunction.SumIf(Range("G:G"), " > " & Date, Range("F:F"))
    End With


    ActiveSheet.Range("C1").Select
        ActiveCell.Value = "Today"
    With TDY
        .Formula = "=Sumif((G:G),Today(),(F:F))"
        .Value = .Value
    End With
    
    ActiveSheet.Range("E1").Select
        ActiveCell.Value = "This Week"
    With TW
        Cells(1, 6) = Application.WorksheetFunction.SumIf(Range("G:G"), " < " & Date, Range("F:F"))
    End With
        
End Sub

Am I missing something?

 

[Marcelo Branco]

Try

With OD
        .Formula = "=SUMIF($G:$G,[COLOR=#ff0000]""[/COLOR]>[COLOR=#ff0000]""[/COLOR] & TODAY(),$F:$F)"
        .Value = .Value
End With

M.

 

[Enzo_Matrix]

Thank you very much,

Works perfectly!

 

[Marcelo Branco]

You are welcome.

M.