speedkreature
New Member
- Joined
- Jan 7, 2014
- Messages
- 2
I'm not to sure how else to title this...
Any help is appreciated. I spend most of my time coding in C and Bash, but this got thrown my way and it's been a struggle the whole way. Some of the code for this project is copied from examples elsewhere, some of it is original.
I'm importing data from CSV's exported by our ERP. The data requires a number of modifications before it can be submitted to the next stage of processing in a third party system. One of the columns contains currency (US dollar) values.
In the CSV, the values are represented as:
155.00
134.97
1389.18
etc.
Once they've been imported from the CSV, Excel strips trailing zeros unless the column is formatted as a currency. What I actually need, using the example values above is:
0000015500
0000013497
0000138918
I thought about multiplying the values by 100 then formatting them with "0000000000" but I can't get the multiplication to work out--syntax errors I'm sure. Below is the relevant area of code where I'd want to put this function.
At this stage I'm looking for any way I can get the formatting I need to work with my data.
Any help is appreciated. I spend most of my time coding in C and Bash, but this got thrown my way and it's been a struggle the whole way. Some of the code for this project is copied from examples elsewhere, some of it is original.
I'm importing data from CSV's exported by our ERP. The data requires a number of modifications before it can be submitted to the next stage of processing in a third party system. One of the columns contains currency (US dollar) values.
In the CSV, the values are represented as:
155.00
134.97
1389.18
etc.
Once they've been imported from the CSV, Excel strips trailing zeros unless the column is formatted as a currency. What I actually need, using the example values above is:
0000015500
0000013497
0000138918
I thought about multiplying the values by 100 then formatting them with "0000000000" but I can't get the multiplication to work out--syntax errors I'm sure. Below is the relevant area of code where I'd want to put this function.
Code:
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each thing In Sheets(1).Range("E1:E" & lastRow)
If thing <> "" Then
Cells(thing.Row, "D") = Cells(thing.Row, "E")
Cells(thing.Row, "E") = ""
' TODO: Take data from D:thing.Row, multiply by 100, and replace. We'll format latter.
End If
Next
At this stage I'm looking for any way I can get the formatting I need to work with my data.