Macro ActiveSheet.Formula Object doesn't support this property or method error

[starheartbeam]

I am trying to do the following:

ActiveSheet.Formula = "=IF(TEXT(E2,""YYYYMM"")< text(m2,""yyyymm""),b2&""-""&c2&""-""&d2&""-""&""01-""&(text(m2,""mmm""))&""-""&(text(m2,""yy"")),b2&""-""&c2&""-""&d2&""-""&e2)"



This works when I put it in an excel formula but when I put it in my macro I get "Object doesn't support this property or method" error and I do not know what I need to do to get this to work? Any help would be very helpful.

 

[Joe4]

You need to provide the range also. Otherwise, it does not know where on the Active Sheet you want the formula, i.e.

[COLOR=#333333]ActiveSheet.[/COLOR][COLOR=#ff0000]Range("A2")[/COLOR][COLOR=#333333].Formula = "=IF(TEXT(E2,""YYYYMM"")[/COLOR]<text(m2,""yyyymm""),b2&""-""&c2&""-""&d2&""-""&""01-""&(text(m2,""mmm""))&""-""&(text(m2,""yy"")),b2&""-""&c2&""-""&d2&""-""&e2)"
 </text(m2,""yyyymm""),b2&""-""&c2&""-""&d2&""-""&""01-""&(text(m2,""mmm""))&""-""&(text(m2,""yy"")),b2&""-""&c2&""-""&d2&""-""&e2)"

 

[starheartbeam]

It did not let me post my whole code. Here it is:

ActiveSheet.Formula = "=IF(FORMAT(E2,""YYYYMM"")
<
TEXT(M2,""YYYYMM""),B2&""-""&C2&""-""&D2&""-""&""01-""&
(TEXT(M2,""MMM""))&""-""&(TEXT(M2,""YY"")),B2&""-""&C2&""-""&D2&""-""&E2)"

 

[mikerickson]

The forum interface strongly prefers spaces before and after less-than symbols.

(A+5) < (B+1) rather than (A+5)<(B+1)

 

[starheartbeam]

Thank you I will try spaces.

 

[Joe4]

You are still missing the range part, as I mentioned in my initial reply.

 

[starheartbeam]

I put spaces before and after and it still is giving me that same error.

 

[mikerickson]

The spaces are for this forum, they are not needed for Excel VBA.
Did you add the range object that Joe4 is talking about?

 

[starheartbeam]

I added the range and it still gives me the same error.

 

[Joe4]

Things can get tricky when you are trying to add in a formula that has literal quote marks, as those are also used for Text delimiters in VBA. If you do not do it correctly, VBA cannot tell if you want literal quotes marks, or you are just denoting text.

I find that the easiest thing to do in these situations is to turn on your Macro Recorder, and enter one of these formulas on your spreadsheet. Then stop the Macro Recorder and view your recorded code. Thois will show you EXACTLY how the formula needs to look in VBA. So you can just copy and paste that recorded formula into your VBA code.