Solved: Need help to change column to comma style

[jaydee]

Hi,

Sorry to bother everyone, but I'm having a problem with the excel file attached. If you look in column C, you'll notice they contain numbers, however they are showing up as text (they will not sum and if you highlight some of the numbers, excel shows the count property).

The only way I can get around it is to highlight the column, click the currency button, and for each cell press "F2 + enter". Then it changes it to an accounting format.

I tried:

With activecell
.clearformats
.style = "comma"
.select
.activate
end with

but none of that seems to work. The file is being pulled from an ad hoc report if that might be the cause.

I'm trying to figure it out as you can imagine it gets tedious if I had hundreds of them to go through.

Thank you for your help in advance.

[Bob Phillips]

Columns("C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("C1").Value = ""

[jaydee]

Thanks xld, I really appreciate your help.