Newbie - Macro Help

[Yellowbird]

I have an Excel file that was created via export from Crystal Reports
(and data pulled from SQL). I now need to Save As CSV, but when I do,
my data is not formatted properly. I think I can probably create a
macro to run while the data is in Excel (prior to CSV format), but I'm
new to macro programming, so am looking for a little guidance. Here's
what I have and what I need to do.

My data in Excel looks like this:

CustomerID,InvoiceTax,InvoiceFreight,InvoiceTotal,LineNbr,UnitPrice,EndOfLine
AB000102,1.56,6.59,29.65,00001,10.75,!

When I Save As CSV, all of my currency fields (columns 2, 3, 4 and 6)
have an extra space before the comma, as follows:

CustomerID,InvoiceTax,InvoiceFreight,InvoiceTotal,LineNbr,UnitPrice,EndOfLine
AB000102,1.56 ,6.59 ,29.65 ,00001,10.75 ,!

So...I want to create a macro to do the following:

1) Remove the extra space before the comma in all currency fields (or
change the cell formatting to Custom ###0.00) for columns 2, 3, 4, and
6 in the above example.

2) Remove the leading zero in position 1 of column 5 so that the
number is only 4 characters long instead of 5. The first position in
this column will always be the extra zero right now.


This will ensure that my CSV output will look like:

CustomerID,InvoiceTax,InvoiceFreight,InvoiceTotal,LineNbr,UnitPrice,EndOfLine
AB000102,1.56,6.59,29.65,0001,10.75,!

Any guidance or assistance is greatly appreciatd.

Thanks in advance,
Yellowbird

 

[Rick]

Hi,

I had to do this years ago, when I saved the Oracle data
as a CSV file. To remove the leading zeros I
used "Trim". I'm not sure if this is what you had in mind.

For example, you might have something like this:

Sub TrimZeros()

Dim S1 As Worksheet
Dim A As Integer
Dim StartRow As Integer
Dim LastRow As Integer

Set S1 = Sheets(1)

StartRow = 8
LastRow = 200

For A = StartRow To LastRow

S1.Cells(A, 19).Value = _

Trim(S1.Cells(A, 5).Value) & " " & _

Trim(S1.Cells(A, 6).Value) & " " & _

Trim(S1.Cells(A, 7).Value) & " " & _

Trim(S1.Cells(A, 8).Value) & " " & _

Trim(S1.Cells(A, 9).Value) & " " & _

Trim(S1.Cells(A, 10).Value) & " " & _

Trim(S1.Cells(A, 18).Value)

Next A

End Sub

The "A" is the rows 8 to 200. The numbers 19, 5 to 10,
and 18 are the columns. S1 is just the first sheet. This
is untested, but it should work unless I missed something.

To remove other letters or numbers etc. you can use Left,
Right, or Mid text functions like:

S1.Cells(A, 20).Value = Left(S1.Cells(A, 19).Value, 5)

The help files should give you more detailed explanations
and samples. "Len" is the length of all of the characters
in a cell, which can be useful at times too. Experiment a
little with it....

Rick