Using Dynamic cell reference in cell formula created by VBA

Hello!

Currently, I am attempting to write VBA code that will open a user selected file. In the file, I need 3 things calculated to be imported into the original sheet:
1) Sum of the Qty column when column State="CA""
2) Sum of the Qty column when column State="TX"
3) Sum of the Qty column when column Member= "Walgreens"

Unfortunately, The Qty, State, and Member columns do not stay in the same location each week. I have code that finds their column locations

Dim Qty_Column As Integer
    Dim State_Column As Integer
    Dim Member_Column As Integer
    Set Rng = Range("A1:Z2")
    Qty_Column = Rng.Find("*Qty*").Column
    State_Column = Rng.Find("*State*").Column
    Member_Column = Rng.Find("*Member*").Column

I am having trouble writing cell formulas that can utilize those cell locations

If someone could help me out in writing formulas that will work with the variables above, or if anyone knows of a better method, that would be fantastic.

Thank you for your time,

Grazian2

Some Ideas that failed:

 'Tried and failed : ActiveCell.Value = Evaluate(""=SUMIF(C[" & State_Column & "],""CA"",C[" & Qty_Column & "])"")
    ActiveCell.FormulaR1C1 = "=SUMIF(C[" & State_Column & "],""CA"",C[" & Qty_Column & "])"

All Code:

Sub ChargeBacks_Pull()
'
' ChargeBacks_Pull Macro
'
    Range("B47:E47").Select
    Selection.ClearContents
    
    ChargeFile = Application.GetOpenFilename
    Workbooks.Open (ChargeFile)

    'Finds first open row and pastes new data there
    OpenRow = Cells(Rows.Count, "B").End(xlUp).Offset(1).Row
    
    'Finds the Quy column
    Dim Qty_Column As Integer
    Dim State_Column As Integer
    Dim Member_Column As Integer
    Set Rng = Range("A1:Z2")
    Qty_Column = Rng.Find("*Qty*").Column
    State_Column = Rng.Find("*State*").Column
    Member_Column = Rng.Find("*Member*").Column

    'Uses the avobe references to calculate the sum of the QTY category for 3 conditions
    Cells(OpenRow, 1).Select
    'Tried and failed : ActiveCell.Value = Evaluate(""=SUMIF(C[" & State_Column & "],""CA"",C[" & Qty_Column & "])"")
    ActiveCell.FormulaR1C1 = "=SUMIF(C[" & State_Column & "],""CA"",C[" & Qty_Column & "])"
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.Copy
    Windows("Dashboard_Template").Activate
    Worksheets("Totals").Activate
    Range("B47").Select
    ActiveSheet.Paste
    
    Windows(Dir(ChargeFile)).Activate
    Cells(OpenRow + 1, 1).Select
    ActiveCell.FormulaR1C1 = "=SUMIF(C[" & State_Column & "],""TX"",C[" & Qty_Column & "])"
       Selection.Copy
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Selection.Copy
    Windows("Dashboard_Template").Activate
    Worksheets("Totals").Activate
    Range("C47").Select
    ActiveSheet.Paste
        
    Windows(Dir(ChargeFile)).Activate
    Cells(OpenRow + 2, 1).Select
    ActiveCell.FormulaR1C1 = "=SUMIF(C[Member_Column], ""*Walgreens*"",C[Qty_Column] )"
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.Copy
    Windows("Dashboard_Template").Activate
    Worksheets("Totals").Activate
    Range("D47").Select
    ActiveSheet.Paste
    
End Sub

Thank you for the replies!!

TMshucks, the second to last formula declaration worked great! Thank you for your help.

With Range("H4")
    .Formula = "=SUMIF(" & Columns(State_Column).Address & ",""CA""," & Columns(Qty_Column).Address & ")"
    .Value = .Value
End With

SNB, thank you for your reply as well!