Problems with substitute
 

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

Problems with substitute
 

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

Problems with substitute
 

Sub MakeBetter()
Range("B2:B25").Value = _
Application.Substitute(Range("B2:B25").Value, "'", vbNullString)
End Sub
'--
I don't believe the above will work on entire columns.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"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

Problems with substitute
 

you could try this. i used b1 as an empty cell, but just pick one on your sheet.
change the column and sheet name to your current name.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim rng As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & lastrow)
ws.Range("B1").Copy
rng.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
End Sub

--


Gary


"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

Problems with substitute
 

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

Problems with substitute
 

hi
1. copy a blank cell from somewhere on the sheet. blank..empty...no data.
high light your column of data then past special..Add.
2. i got this code from somebody. not mine but it does work. so to who ever
wrote this code...thank you. if you recognize the code, id yourself so i can
give you proper recognition.
Sub ApostropheKiller()
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = c.Text
End If
Next c
Application.ScreenUpdating = True
End Sub

regards
FSt1

"Chris" wrote:

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

Problems with substitute
 

Hi Chris,

Without VBA, try:

Select the cells of interest
Menu | Data | Text to columns |
Delimited | Nextt | Cancel any delimiters |
Next | Finish


---
Regards.
Norman

"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

Problems with substitute
 

Hi Chris,

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


Neither of my suggestions requires
any looping.



---
Regards.
Norman

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

Problems with substitute
 

hi all, thanks for some great suggestions. Norman's approach worked
perfectly.

Chris


"Norman Jones" wrote in message
...
Hi Chris,

Without VBA, try:

Select the cells of interest
Menu | Data | Text to columns |
Delimited | Nextt | Cancel any delimiters |
Next | Finish


---
Regards.
Norman

"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