Excel Sumif with Changing Criterion

[CosmicCarnage6547]

Hello everyone,

I am having some issues with a Sumif function that I am trying to loop.

Essentially I have text in column A of Sheet1 that I would like to use as my criteria - and the range & sumrange that I would like to use are on Sheet2. I have highlighted the issue in my code below:

Dim Lr, Lc As LongLr = Cells(Rows.Count, "A").End(xlUp).Row
Lc = Cells(1, Columns.Count).End(xlToLeft).Column

For a = 2 To Lr
    Cells(a, 3).Value = Application.WorksheetFunction.SumIf(Worksheets("Sheet 2").Range( _
            "A1:A" & Lc), Worksheets("Sheet 1").Cells(a, 1), Worksheets("Sheet 2").Range( _
            "A1:A" & Lc))
Next a

 My issue is that the values in each cell of Column A change each time (example: A2 = Carrot, A3 = Cabbage), so I would like the sumif to look up a DIFFERENT value in SHEET 1 in the SAME ranges of SHEET 2 during each loop. However I know that the criteria used in Sumif functions are typically values or logic and not an array.

I've tried to think outside of the box on this one, but ultimately I have hit the end of the line. Any and all insight would be appreciated - I really appreciate your time.

[#2]

Okay I have adjusted the code to the following:

Dim Lr, Lc As Long

Worksheets("Sheet 1").Activate

Lr = Cells(Rows.Count, "A").End(xlUp).Row
Lc = Cells(1, Columns.Count).End(xlToLeft).Column

Range("A2").Select
    For a = 2 To Lr
        Cells(a, 2).Value = Application.WorksheetFunction.SumIf(Worksheets("Sheet 2").Range("A1:A" & Lc), ActiveCell.Value, Worksheets("Sheet 2").Range("Lc:Lc"))
      With ActiveCell.Offset(1, 0)
      End With
    Next a

My problem now is the sum range - if I use Range("Lc:Lc"), I get an application or object-defined error. But as the Lc may change depending on the input file, I can't set the column I would like to add in stone. However my assumption is that the sumrange will always be the last column in any given worksheet. How should I go about identifying the sumrange here?