VBA - using nested formulas and custom variables

Question

i would like to use a multi-nested formula to determine if the dates in Column A fall in a specific month/year, using a formula such as =IF(AND(EXACT(TEXT(A8,"mmm"),"Jan",EXACT(TEXT(A8,"yyyy"),"2018"),"Y","N") However to make this formula more dynamic in VBA, i would like to replace the "Jan" with a custom month that i define via a pop-up input box, hence

Dim mth As String
FormulaR1C1= "=IF(AND(EXACT(TEXT(RC[-2],""mmm"")," & mth & ",EXACT(TEXT(A8,""YYYY""),""2018""),""Y"",""N"")"

When i do this, the formula fails to resolve properly because the "Jan" term appears without the quotes and so the two do not match: =IF(OR(EXACT(TEXT(A9,"mmm"),Jan)),"Y","N")

How can i modify the code to obtain "Jan" in my output please?

Thank you

edit: Dim mth As String mth = "Jan"

Set FormulaRange = Range("C7:C15") FormulaRange.FormulaR1C1 = "=IF(AND(EXACT(TEXT(RC[-2],""mmm""),""" & mth & """,EXACT(TEXT(A8,""YYYY""),""2018""),""Y"",""N"")"

Probably a duplicate stackoverflow.com/q/34862502/1531971

user1531971
– user1531971

2018-07-10 16:12:36 +00:00
Commented Jul 10, 2018 at 16:12 — user1531971
– user1531971, Commented Jul 10, 2018 at 16:12

Michał Turczyn · Accepted Answer · 2018-07-10 16:49:43Z

1

To put double-quotes in a string in VBA you have to use double qoutes "", i.e. when you write """" it means that is that string containing one double-quote, hence your code should look like this:

Dim mth As String
FormulaR1C1 = "=IF(AND(EXACT(TEXT(RC[-2],""mmm""),""" & mth & """),EXACT(TEXT(A8,""YYYY""),""2018""),""Y"",""N"")"

edited Jul 10, 2018 at 16:49

answered Jul 10, 2018 at 15:45

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Taeyeonist Over a year ago

edit: i tried using the suggested line of code but got an application/object defined error

Rory Over a year ago

There's a closing bracket missing (in the OP too) from the first EXACT function.

Cullen · Accepted Answer · 2018-07-10 16:57:59Z

0

If I understand right, you're trying to check if dates in column A are in a specific month, yes? Use

if MonthName(Month(c.value)) = userInput then
    ...
end if

for some Cell c and String userInput. You might also need to use CDate(c.value) depending on the format your dates are in.

answered Jul 10, 2018 at 16:57

Cullen

545 bronze badges

Collectives™ on Stack Overflow

VBA - using nested formulas and custom variables

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related