VBA - Struggling with " characters (quotation marks in formula)

[velohead]

Hi All,

Ultimately, I want a piece of code to insert the following excel formula into cell AX2.
="WE-"&YEAR(E2)&"-"&TEXT(E2,"MM")&"-"&TEXT(E2,"DD")


my code at the moment is...

Sub D207_AX2_Only()
Range("AX2").Select
ActiveCell.Formula = "=""WE-""&YEAR(E2)"
End Sub

the result in excel is ="WE-"&YEAR(E2)
As you can see, the VB code contains more " characters than the result.
This is correct, as I in VB one needs to 'wrap' the 'text bit' in " characters.

I'm struggling to build this up though.
Getting bambozzled with how many " to make it work

 

[dreid1011]

Give this a try:

"=""WE-""&YEAR(E2)&""-""&TEXT(E2,""MM"")&""-""&TEXT(E2,""DD"")"

 

[velohead]

Give this a try:

"=""WE-""&YEAR(E2)&""-""&TEXT(E2,""MM"")&""-""&TEXT(E2,""DD"")"

Yes, that works.
I'll have to count/examine the sequence of " to see where I went wrong.

Thank You dreid1011 !

 

[Peter_SSs]

Another option is to include all the formatting in one string instead of many.

ActiveCell.Formula = "=TEXT(E2,""W\E-yyyy-mm-dd"")"

Also, unless you specifically want to select AX2 first, there is no need to select.

Sub D207_AX2_Only()
  Range("AX2").Formula = "=TEXT(E2,""W\E-yyyy-mm-dd"")"
End Sub

 

[velohead]

Another option is to include all the formatting in one string instead of many.

ActiveCell.Formula = "=TEXT(E2,""W\E-yyyy-mm-dd"")"

Also, unless you specifically want to select AX2 first, there is no need to select.

Sub D207_AX2_Only()
  Range("AX2").Formula = "=TEXT(E2,""W\E-yyyy-mm-dd"")"
End Sub

Nice, both would streamline my code.
Old habits die hard, I tend to select the cell when I know I can just manipulate it without selecting it. D'oh!
Well suggested, thank you.

 

[Peter_SSs]

You're welcome.

I tend to select the cell when I know I can just manipulate it without selecting it.

So long as you are aware & remember that selecting is a (relatively) slow process and if there is a lot of it in your code then the code may run noticeably slower than without the selecting.

 

[velohead]

You're welcome.


So long as you are aware & remember that selecting is a (relatively) slow process and if there is a lot of it in your code then the code may run noticeably slower than without the selecting.

Good point. I was not aware, but perhaps should have been.