Problems with substitute

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

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
 
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
'<<==========
 
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" <[email protected]>
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
 
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
 
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
 
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
 
Hi Chris,

Without VBA, try:

Select the cells of interest
Menu | Data | Text to columns |
Delimited | Nextt | Cancel any delimiters |
Next | Finish
 
Hi Chris,
thanks Norman...I've written this which works but how do I loop through
all the values in the column?
Click to expand...


Neither of my suggestions requires
any looping.
 
hi all, thanks for some great suggestions. Norman's approach worked
perfectly.

Chris


Norman Jones said:
Hi Chris,

Without VBA, try:

Select the cells of interest
Menu | Data | Text to columns |
Delimited | Nextt | Cancel any delimiters |
Next | Finish
Click to expand...
 
Back
Top