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("C 4")
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,'[C omisiones 2017.xlsm]Stats17'!$E$3:$E $10000,C2, '[Comision es 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
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("C
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
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"
Thank you in advance for any help you can provide to me,
Best Regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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!
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
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!
ASKER
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
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?
Is it dates formatted to appear as you want them in the formula?
ASKER
C3 is the date the user picks from a deployable list, and O1 is the RIGHT(Year,2) function from C3
Thanks
Thanks
ASKER
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!
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)"""
ASKER
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!
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.
Glad we're able to help you.
ASKER
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