SUMIFS ranges in a nested loop question

[Reziukas]

Dear All,

Pretty much a total noob here

I'm trying to build a sumifs function with two conditions (transaction date and transaction code which are in columns of unique values) into a nested loop. tr_date and tr_code return string values which are a date and an alphanumeric code which seem to return correct values in watch window, but the sumifs returns 0 values for all but 3 codes (which repeat more than 3 times) from a list of 300+ codes of 7 types so I'm guessing there may be something wrong with how I did sumifs ranges. Any help is much appreciated. Code below

Sub trying3()


Worksheets("Report").Activate


Application.DisplayAlerts = False


Dim last_date As Long
last_date = Range("AC1").End(xlDown).Row


Dim date_count As Long
date_count = 2


Dim tr_date As String
tr_date = Range("AC" & date_count)


Dim last_code As Long
last_code = Range("AD1").End(xlDown).Row


Dim code_count As Long
code_count = 2


Dim tr_code As String




Dim tgt_rng_cnt As Long
tgt_rng_cnt = 2


Dim i As Long
i = 2


Dim ppdate As Range
Dim ppval As Range
Dim code As Range


Set ppdate = Worksheets("Report").Range("D2:D20000")
Set ppval = Worksheets("Report").Range("G2:G20000")
Set code = Worksheets("Report").Range("J2:J20000")


    For date_count = 2 To last_date
        
        For code_count = 2 To last_code
                            
                           tr_date = Range("AC" & date_count)
                           
                           tr_code = Range("AD" & code_count)
                            
                            Cells(i, 31) = tr_code
                            Cells(i, 32) = tr_date
                            Cells(i, 32) = Format(Cells(i, 32), "dd/mm/yyyy")
                            Cells(i, 33) = Application.WorksheetFunction.SumIfs(ppval, ppdate, tr_date, code, tr_code)
                     
                     i = i + 1
                               
            Next code_count
      
        Next date_count

End Sub

P.S. I'm pretty sure there's a "trash" variable there, I just can't be bothered to take it out tonight

 

[gsistek]

Try changing
Dim tr_date as String
to
Dim tr_date as Long

Also, you can combine

                            Cells(i, 32) = tr_date
                            Cells(i, 32) = Format(Cells(i, 32), "dd/mm/yyyy")

to

                            Cells(i, 32) = Format(tr_date, "dd/mm/yyyy")