Link to home
Start Free TrialLog in
Avatar of Carlos Casio
Carlos Casio

asked on

Insert variables into SUMIFS Formula

Good day everybody,

Even though I've tried many different syntax, I don't get to work the following code:
I define Year and Year1 as variables, where year is 2017 and Year1 is 17
Year= ActiveSheet.Range("C3")
Year1=ActiveSheet.Range("C4")

Then, there's a part of the code where I need to use a SUMIFS to other workbook already open, the original formula is:
=SUMIFS('[Comisiones 2017.xlsm]Stats17'!$F$3:$F$10000,'[Comisiones 2017.xlsm]Stats17'!$E$3:$E$10000,C2,'[Comisiones 2017.xlsm]Stats17'!$B$3:$B$10000,G2)

So, the only thing I want to do is that "2017" and "17" can be defined into the formula with both variables, since the names of the files and sheets will be changing depending on the current year.

I had this problem before and I solved it by inserting, litterally something like: "SUMIFS('[Comisiones "&Sheets("MXN").Range("C3")&"..." as if it took the variable directly from the workbook, however, this solution only works for me when the formula and the referenced cell are on the same workbook, and now, the reference workbook is different from the workbook I want to set the formula in.

Thank you in advance for any help you can provide to me,

Best Regards
SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Carlos Casio
Carlos Casio

ASKER

What I understand from that is it will take the date from today and then show it only as "YYYY" or "YY", if so, I would only have a problem: the year number it's not supposed to be taken from the day the macro is run, but from the date you choose before running the macro on an specific cell.

To be clear, the file gets information from many other Excel files, gathers it, calculates some stuff (like this formula), and then paste as values. However, there are monthly databases that can change from time to time so, if today I get to know the database from January 2016 changed, I would like the code to take the 2016 from an specific cell I set, adding the value to the variable and then set the formula.

I think the code you gave me it is very functional but I still have that problem.

Tyvm
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help,

I already tried this, and it works, it writes the value filled in the variable. However, variables 'year' and 'year1' are not getting the values I want.

Here is what I have:
year= Sheets("Macros").Range("C3")
year1=Sheets("Variables").Range("O1")
Msgbox year
Msgbox year1

Open in new window


And then the values are:
Year = 09/07/1905
Year1=16/01/1900

I already checked there are values on those 2 cells and there are, so I don't know what's happening,

Thanks!
Ok, there is an update, I'm sorry, before year and year1 there was a code to dim both as date.

I erase that and then it says:

Compile error:
Argument not optional

Thanks again
What exactly is in C2 on the Macros sheet and O1 on the Variables sheet?

Is it dates formatted to appear as you want them in the formula?
C3 is the date the user picks from a deployable list, and O1 is the RIGHT(Year,2) function from C3

Thanks
Ok so I did:
Dim year as string
dim year1 as string

And it works perfectly, however I really needed to workout on the formula so I could insert the variables, and I thank you for that help

Greetings!
Try as below:
Dim Yr1 As Date
Dim Yr2 As Date
Dim Result As Variant
Yr1 = Format(Sheets("Macros").Range("C3"), "yyyy")
Yr2 = Format(Sheets("Variables").Range("O1"), "yy")
Result = "=SUMIFS('[Comisiones " & Yr1 & ".xlsm]Stats" & Yr2 & "'!$F$3:$F$10000,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & " '!$E$3:$E$10000,C2,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & "'!$B$3:$B$10000,G2)"""

Open in new window

The final answer would be:
dim year as string
dim year1 as string
year = (whatever reference you give)
year1= (same as year)

Activecell.formula= "=SUMIFS(text"& variable (year/year1)&"more text..." and so on,

Both helped me get this, thanks!
Great minds thinks alike :)
Glad we're able to help you.