I have the following code which is part of a much larger macro which is converting a number so that the the trailing zero in the number 49.50 appears in the formula bar. This is essential as the eventual csv file links to an external printing program which only prints what is sees.
My problem is that this fix works when the file is saved in excel format, but when I save it in CSV format the number reverts to 49.5. How can I convert the original number which appears as 49.5 in the formula bar and is viewed a 00000050 in the file that the sent through. I cannot convert using the text to columns when the file is opened as the other codes in the original file need to maintain the original formatting.
Any assistance in this matter would be greatly appreciated
Kind regards
My problem is that this fix works when the file is saved in excel format, but when I save it in CSV format the number reverts to 49.5. How can I convert the original number which appears as 49.5 in the formula bar and is viewed a 00000050 in the file that the sent through. I cannot convert using the text to columns when the file is opened as the other codes in the original file need to maintain the original formatting.
Any assistance in this matter would be greatly appreciated
Kind regards
Code:
Sub mc003()
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If ActiveSheet.Name = "MC003" Then
rw = Range("a65536").End(xlUp).row
Range("R2:R" & rw).FormulaR1C1 = "=TEXT(RC[-7],""000.00"")"
Range("R2:R2").EntireColumn.Copy
Range("R2:R2").EntireColumn.PasteSpecial xlPasteValues
Range("S2:S" & rw).FormulaR1C1 = "=IF(LEFT(RC[-1],1)=""0"",REPLACE(RC[-1],1,1,""""))"
Range("S1").Value = "GBP PRICE"
Range("S:S").EntireColumn.Copy
Range("K:K").EntireColumn.PasteSpecial xlPasteValues
Columns("R:S").Delete
End If
Next ws
End Sub