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

starheartbeam

New Member
Joined
Aug 8, 2018
Messages
16
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,483
Office Version
365
Platform
Windows
You need to provide the range also. Otherwise, it does not know where on the Active Sheet you want the formula, i.e.
Code:
[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

New Member
Joined
Aug 8, 2018
Messages
16
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)"
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,570
The forum interface strongly prefers spaces before and after less-than symbols.

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

starheartbeam

New Member
Joined
Aug 8, 2018
Messages
16
Thank you I will try spaces.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,483
Office Version
365
Platform
Windows
You are still missing the range part, as I mentioned in my initial reply.
 

starheartbeam

New Member
Joined
Aug 8, 2018
Messages
16
I put spaces before and after and it still is giving me that same error.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,570
The spaces are for this forum, they are not needed for Excel VBA.
Did you add the range object that Joe4 is talking about?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,483
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,182
Messages
5,473,001
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top