thanks Norman...I've written this which works but how do I loop through all
the values in the column?

Sub RemAPOS()
Dim apos As String
Workbooks("MMIT PID.xls").Activate 'relevant workbook
Worksheets("Register").Select
apos = ActiveSheet.Range("b2").Value 'want this to go
through all values b:b
With WorksheetFunction
apos = trim(.Substitute(apos, "'", ""))
ActiveSheet.Range("b2").Value = apos
End With
End Sub


"Norman Jones" wrote in message
...
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