Hi Chris,

Try something like:
'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheee2") '<<==== CHANGE
Set Rng = SH.Range("A1:A100") '<<==== CHANGE

With Rng
.NumberFormat = _
"#,##0.00_);(#,##0.00)" '<<==== CHANGE
.Value = .Value
End With

End Sub
'<<==========



---
Regards.
Norman


"Chris" wrote in message
...
Hi all...

I import some data into a worksheet column but instead of a number I get
and error saying values may have an apostophe preceding the value and it
is not seen as a number. I have the formula below which works within the
sheet but want to automate it in VBA for the whole column:

=IF(RIGHT(B2,1)="'",SUBSTITUTE(B2,"'",)*-1,B2)

I cannot work this out and I can't find a way to convert the imported
value to a number.

Would really appreciate some help on this...thx