CODEWITHSUNDEEP
vbaexcel
Taeyeonist
Taeyeonist

Reputation: 11

VBA - using nested formulas and custom variables

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"")"

Upvotes: 1

Views: 231

Answers (2)

Cullen
Cullen

Reputation: 54

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.

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

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"")"

Upvotes: 1

Related Questions