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
Bookmarks