Formatting Spaces in Cell with VBA

[lsteffen]

Good morning,

Can someone please help with this code?

Dim NumSpaces As Long
NumSpaces = 14
Range("G1").Value = [B1].Value & Application.Rept(" ", NumSpaces) & [B1].Value

This is giving me an ending value of

650********650

and it should be giving me

***********650

Where the * are spaces.

Thank you,
Liz

 

[Tom Urtis]

What else would you be expecting? Your code line is this
[B1].Value & Application.Rept(" ", NumSpaces) & [B1].Value
so you have 650 twice (presumably what is in cell B1), separated by the spaces.

instead, do this
Range("G1").Value = Application.Rept(" ", NumSpaces) & [B1].Value

 

[lsteffen]

Thank you for your reply. When I update my code to what you suggest, it does not put in the spaces.

Liz

 

[jmthompson]

I'm sure Tom is about to respond, but if G1 isn't formatted as Text, it will drop the preceeding zeroes.

If you don't want to worry about formatting, you could alter your code to
(untested)

"'" & Application.Rept(" ", NumSpaces) & [B1].Value

 

[lsteffen]

Formatting as text did the trick.

Thank you,
Liz

 

[Tom Urtis]

Thanks JT