Using Dynamic cell reference in cell formula created by VBA

[grazian2]

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

All Code:

Please Login or Register  to view this content.

[TMS]

Some different approaches:

Please Login or Register  to view this content.

Regards, TMS


Edit: Test Data

[snb]

Please Login or Register  to view this content.

[grazian2]

Thank you for the replies!!

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

Please Login or Register  to view this content.

SNB, thank you for your reply as well!

[TMS]

You're welcome. Thanks for the rep.


I like that approach as you can always comment out the ".Value = .Value" to see if the formula is what you were expecting. But, horses for courses


Regards, TMS