Trouble with a blank " " value as part of a VBA function

I'm trying to use the REPT function in a macro, but I can't find a way to represent the " ". I've tried "" "" and "' '", but it generates an error every time.

Yes, I do have valid values for the other variables. If I replace the " " with "0" for example, it runs fine and pads the string with zeroes. I need it to pad with blanks. I've highlighted the trouble area with red, I think.

Range(NewColumnLetter & x).Value = "=" & "Rept(" & " " & "," & TotalCharacters & " - LEN(" & OldColumnLetter & x & "))&" & OldColumnLetter & x

hi,

Please Login or Register  to view this content.

Try Char(0)

Maybe the CHAR options would work too, but """ """ worked perfectly. Probably the only combo I didn't try. Thanks.