1

I am reading a file with currency values and creating strings of data to print into a text file. I discovered that I needed to use the VALUE2 option in order to stop the macro from rounding the values. However, I have now run into the problem that if any of the trailing digits are zeros, they are dropped. I need the macro to keep all digits.

For example, if the cell has $10.50 formatted as currency, the code below reads 10.5 into payAmountTemp, but I need 10.50.

payAmountTemp = Cells(j, 5).Value2

What would be the best way to go about capturing the data correctly? Just FYI, I must then further format the data for the text file for a 7 digit field with no decimal and with leading zeros, so I am using the code below next.

payAmount = String(7 - Len(Replace(payAmountTemp, ".", "")), "0") & Replace(payAmountTemp, ".", "")

In the example above, I am getting 0000105 where I need 0001050.

Jay
  • 125
  • 1
  • 13

1 Answers1

0

For the second decimal:

Format(payAmountTemp, "#0.00")

for the seven places

Format(payAmountTemp * 100, "0000000;-000000")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Wow. Such a nicer looking way to get the seven digits. Thanks. – Jay Oct 26 '15 at 20:00
  • Actually, a problem with this popped up regarding negative numbers. It places the negative sign before the leading zeros and does not count it as a digit. So -15.25 became -0001525. While I am not sure if the negative sign before the zeros is ok or not, I do know that it requires only 7 characters for the length. The second decimal format is exactly what I need for another issue though. – Jay Oct 26 '15 at 20:23
  • Then use an If statement to test whether negative and use another line that only has six 0s. – Scott Craner Oct 26 '15 at 20:42
  • @Jay I have fixed the one statement, it does not need an if statement. I will automatically make the difference. – Scott Craner Oct 26 '15 at 21:01