VBA Code Modify Format Cell to have spaces at end for right justification

[OilEconomist]

Thanks in advance for your assistance. How would I modify the following line to get how many ever spaces I would like at the end of it. In this case let's say two so it changes from what I have in the first cell (D1) to the second cell (D2). They are both right justified, but D2 has two spaces at the end to ensure that it does not bunch up with what's in column E. See the code below the image.

This is what I have to format the cell currently. How would I modify?

Range("D" & i).NumberFormat = "[>=1000000] #,##0.0,,"" m"";[>0] #,##0.0,"" k"";General"

 

[Jamie McMillan]

in cell D2 type =d1&" " - Two spaces in the text brackets

VBA code is similar

Range("D2").Value = Range("D1").Value & " "

Jamie

 

[Peter_SSs]

What about having no spaces at the right and format the whole column like this, thereby letting Excel do the alignment for you?

 

[OilEconomist]

in cell D2 type =d1&" " - Two spaces in the text brackets

VBA code is similar

Range("D2").Value = Range("D1").Value & " "

Jamie

Apologies actually I should have been clearer. I wanted to add spaces by showing them, but not actual spaces. I think I figured it out. See below.

 

[OilEconomist]

What about having no spaces at the right and format the whole column like this, thereby letting Excel do the alignment for you?

View attachment 52352

I think I figured it out and it was what you are saying here - no spaces, but aligning it that way. See my solution below.

 

[OilEconomist]

Thanks in advance for your assistance. How would I modify the following line to get how many ever spaces I would like at the end of it. In this case let's say two so it changes from what I have in the first cell (D1) to the second cell (D2). They are both right justified, but D2 has two spaces at the end to ensure that it does not bunch up with what's in column E. See the code below the image.

View attachment 52346

This is what I have to format the cell currently. How would I modify?

Range("D" & i).NumberFormat = "[>=1000000] #,##0.0,,"" m"";[>0] #,##0.0,"" k"";General"

I think my original post might have not been clear as I wanted to do formatting by right justifying and have some room between the two columns, but not with actual spaces. Just look like spaces.

I changed the following:

Range("D" & i).NumberFormat = "[>=1000000] #,##0.0,,"" m"";[>0] #,##0.0,"" k"";General"

to the following (see where "" m"" becomes "' m "" and "" k"" becomes "' k "" (i.e. put spaces before the last quote)

Range("D" & i).NumberFormat = "[>=1000000] #,##0.0,,"" m  "";[>0] #,##0.0,"" k  "";General"

 

[Peter_SSs]

I think I figured it out and it was what you are saying here

No, your solution is not what I was saying but yours looks fine anyway.