Inserting colon as text

[iashorty]

I am using the following
ActiveCell.FormulaR1C1 = "=CONCATENATE(PriorYear&" - "&PriorMonth&":
"&VLOOKUP(RC[-26],'Prior Month'!R9C1:R1009C12,12,FALSE),CHAR(10),'Prior
Month'!RC[-17])"

to pull in prior month commentary and adding a date stamp with a COLON
between the two.
The above should look like: 2007-08: This situation...
The colon in not being accepted. What is the string to insert a colon as
text in this formula?

 

[iashorty]

If I changed the : to a -, I could get past the initial compiling error.
So I may have an additional problem. I also have a "Run time error '13';
type mismatch"
Please respond to both errors.

 

[Dave Peterson]

Are PriorYear and PriorMonth variables in your code or are they names in your
workbook?

I am using the following
ActiveCell.FormulaR1C1 = "=CONCATENATE(PriorYear&" - "&PriorMonth&":
"&VLOOKUP(RC[-26],'Prior Month'!R9C1:R1009C12,12,FALSE),CHAR(10),'Prior
Month'!RC[-17])"

to pull in prior month commentary and adding a date stamp with a COLON
between the two.
The above should look like: 2007-08: This situation...
The colon in not being accepted. What is the string to insert a colon as
text in this formula?

 

[iashorty]

They are range names to specific cells. These cells change based on an input
screen.

i.e. input screen may have the current month and year and these cells show
the previous month and year associated with that month. So if I am working on
2008-06 PriorYear will show 2008 and PriorMonth will show 05.

The purpose is to bring in comments from the prior month, if applicable, to
the current month data.

Are PriorYear and PriorMonth variables in your code or are they names in your
workbook?

I am using the following
ActiveCell.FormulaR1C1 = "=CONCATENATE(PriorYear&" - "&PriorMonth&":
"&VLOOKUP(RC[-26],'Prior Month'!R9C1:R1009C12,12,FALSE),CHAR(10),'Prior
Month'!RC[-17])"

to pull in prior month commentary and adding a date stamp with a COLON
between the two.
The above should look like: 2007-08: This situation...
The colon in not being accepted. What is the string to insert a colon as
text in this formula?

 

[Dave Peterson]

Maybe:

ActiveCell.FormulaR1C1 = "=PriorYear&"" - ""&PriorMonth&"":""&" _
& "VLOOKUP(RC[-26],'Prior Month'!R9C1:R1009C12,12,FALSE)" _
& "&CHAR(10)&'Prior Month'!RC[-17]"

I dropped the =concatenate() and just used the & operand to join the text.

And when you're using double quotes in strings in VBA, you double them up ("")
to get a single double quote (").

They are range names to specific cells. These cells change based on an input
screen.

i.e. input screen may have the current month and year and these cells show
the previous month and year associated with that month. So if I am working on
2008-06 PriorYear will show 2008 and PriorMonth will show 05.

The purpose is to bring in comments from the prior month, if applicable, to
the current month data.

Are PriorYear and PriorMonth variables in your code or are they names in your
workbook?

I am using the following
ActiveCell.FormulaR1C1 = "=CONCATENATE(PriorYear&" - "&PriorMonth&":
"&VLOOKUP(RC[-26],'Prior Month'!R9C1:R1009C12,12,FALSE),CHAR(10),'Prior
Month'!RC[-17])"

to pull in prior month commentary and adding a date stamp with a COLON
between the two.
The above should look like: 2007-08: This situation...
The colon in not being accepted. What is the string to insert a colon as
text in this formula?