Ytukyg

I am attempting to create a template that will pull data from multiple files that I will save down in my documents. The template will pull data and utilize a SumIf function to reference a snack on sheet 1, and sum the numbers next to it on sheets 2 & 3. My assumption is that the snack will always be in column A, but depending on the file I download, the "Quantity" column may differ.

The code below tries to reference Lc as the sum range, but through tinkering with the language, I am getting

Error '1004': Application-defined or object-defined error -or-
Error '13': Type Mismatch

I have thought about a range.address, but I'm unsure of how to go about it. Is it necessary? Any insight would be very appreciated.

Snacks:

Dim Lr, Lc As Long



Lr = Cells(Rows.Count, "A").End(xlUp).Row

Lc = Cells(1, Columns.Count).End(xlToLeft).Column

For a = 1 To Lr



    Cells(a, 3).Value = Application.WorksheetFunction.SumIf(Worksheets("Sheet 2").Range( _

            "A2:A" & Lc), Worksheets("Sheet 1").Cells(a, 1), Worksheets("Sheet 2").Range( _

            "A2:A" & Lc))



Next a

--------------------------------Update--------------------------

Thanks for the valuable insight guys.

@QHarr I really appreciate the insight. My question is, how to I create a "single criteria" when I want the criteria to change each time it loops? Essentially I want it to sum "Cheetos" initially, but next time I would like it to sum "Doritos". You will see above that I have swapped to cells(a, 1) instead of the Range("A:A") for the criteria. I am getting a value of 0 because I don't think the cells(a, 1) is looping in the same way that the overarching loop is.

@Michal Rosa Thank you, I am no longer getting the error - it is running without error, but all of the SumIf values are returning as "0". Also, great catch on the LastRow issue - after working thru this for a while, it's the small things that catch me out.

It's not a terribly efficient way of doing things, but if you really want to do your calculations this way, your code should look as follows:

Dim l As Long



l = Cells(Rows.Count, "A").End(xlUp).Row



For a = 2 To l



    Cells(a, 3).Value = Application.WorksheetFunction. _

                    SumIf(Worksheets("Sheet 1").Range("A2:A" & l), _

                    Cells(a, 1).Value, _

                    Worksheets("Sheet 1").Range("C2:C" & l)) + _

                        Application.WorksheetFunction. _

                    SumIf(Worksheets("Sheet 2").Range("A2:A" & l), _

                    Cells(a, 1).Value, _

                    Worksheets("Sheet 2").Range("C2:C" & l))



Next a

Assuming your data inputs are in sheets "Sheet 1" and "Sheet 2", in columns A and C.