Thread: Adding 0 to the end of selected cells

Hello all,

I need some help with this probably very simple macro. I get an excel sheet with data that is made up of 5 digits. However i need to change this to 6 digits by adding a 0 at the end. The data is contained in two seperate colums. I have uploaded an example of the data. Column A is not important and can be ignored. The data in columns B and C needs to be converted into the 6 digits with trailing 0.

I have searched around the internet and got thrown from one site to the next with possible solutions but nothing that really does what i want. It's probably out there somewhere i just cant find it.

Any help with this will be greatly appreciated.

Thanks for the help!

I was playing around with the suggestions you gave. When i convert the format to a number and run the command you gave nothing happens. This is the complete code:

Sub Add0()
Range("B2:C14").NumberFormat = "0.000"
End Sub

I dont get any error messages so it seems to run however nothing is changed.

I actually came across another problem. There is a dot in the value as well that needs to be removed to be able to import the data correctly into another excel sheet.
I also tried it with the formula and that seems to work. However that still doesnt remove the dot.

Originally Posted by own4utreg

Hello all,

data that is made up of 5 digits. However i need to change this to 6 digits by adding a 0 at the end.

See if this helps,
I added columns D and E with the 0 added to the end.

Originally Posted by snb

Sub snb()
  [B1:C14] = [B1:C14 & "0"]
End Sub

The only way i can get this macro to change anything is when i change the format to a number with 3 decimals. If this is the way to go then thats not a problem. However after that i need to paste the info into another excel sheet which requires the numbers to be without any dots or comma's.

So i think were slowly getting there

Thanks again so far!

Originally Posted by jwright650

See if this helps,
I added columns D and E with the 0 added to the end.

Did this work for you?

Here is a preview from the worksheet:

Originally Posted by snippet from WorkSheet

164.440
485.550

I figured out how to get this to work thanks to all your tips. I combined some of the things you guys mentioned and used this to record a macro in excel. I inserted some colums and used a formula to take the first 3 and last 2 digits of the value and added a 0 at the end. This way i got rid of the comma in between.

For those interested here is the macro that was recorded. Maybe there's some stuff in there thats not really needed but it works and thats whats important right now.

Sub Aanpassen_RD_download()
'
' Aanpassen_RD_download Macro
'
' Sneltoets: Ctrl+n
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,3)&MID(RC[-1],5,2)&""0"""
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C52"), Type:=xlFillDefault
    Range("C2:C52").Select
    ActiveWindow.SmallScroll Down:=-60
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,3)&MID(RC[-1],5,2)&""0"""
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E52"), Type:=xlFillDefault
    Range("E2:E52").Select
    ActiveWindow.SmallScroll Down:=-39
End Sub

Thanks a lot to all the people that helped! As i mentioned before i took some things and combined them so you all helped me solve this problem. You have my gratitude.

Originally Posted by own4utreg

I figured out how to get this to work

Good stuff, thanks for posting your results.....


Note to self: <take down notes for future reference>

Taking notes is always a good thing!