Excel macro problem

New Contributor

We use (for printing) next Excel macro:

Sub Header3()
With ActiveSheet.PageSetup
..........
.RightFooter = "&18" & Range("C1").Text + Chr(13) + "&12" & Range("C2").Text + Chr(13) + "Variant:" + " " + Range("E10").Text + Chr(13) + Range("C3").Text
End With

End Sub

 

The Macro works correctly if the value of C1 cell starts  with a letter. 

The Macro works incorrectly if value of C1 cell starts with a digit.

The Macro works correctly too if value of C1 cell starts with a digit and line is: 

.RightFooter =  Range("C1").Text +......

Please to find the attached files.

How to solve this problem?

 

Best Regards,

Saulius

 

 

2 Replies

@Saulius_TR 

The problem is that & followed by digits specifies the font size. If your text begins with a digit, Excel treats that digit as part of the font size.

Try inserting something in between the font size and the text., for example &L. This left-aligns the text, so it doesn't do much, but it does separate the font size from the text:

 

.RightFooter = "&18&L" & Range("C1").Text & ...

@Hans Vogelaar 

 

Dear Hans,

Thank You for help.

The font size is incorrect, if I use 

 

.RightFooter = "&18&L" & Range("C1").Text & ...

 

But Your idea was right,  insertion spacebar after font size solves the problem

.RightFooter = "&18 " & Range("C1").Text & ...