How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value' to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

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

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want to
check all cells) without writing a large range?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

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

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)
--
I am very thankful for any fast assistance!


"SamanthaK" skrev:

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want to
check all cells) without writing a large range?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

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

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Hi Samantha,

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want
to
check all cells) without writing a large range?

Change:

Set rng = SH.Range("A1:D30") '<<===== CHANGE

to

Set rng = SH.UsedRange

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)

Change:

If Not .Value Like "*[A-Z]*" Then

to
If Not UCase(.Value) Like "*[A-Z]*" Then

---
Regards,
Norman

Thank you so much!!!! They both work but when I use

Set rng = SH.UsedRange

instead of writing

Set rng = SH.Range("A1:D30")

the processing time becomes very very long. Why is that?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want
to
check all cells) without writing a large range?

Change:

Set rng = SH.Range("A1:D30") '<<===== CHANGE

to

Set rng = SH.UsedRange

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)

Change:

If Not .Value Like "*[A-Z]*" Then

to
If Not UCase(.Value) Like "*[A-Z]*" Then

---
Regards,
Norman

Hi
try

Code:
--------------------

Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
.Pattern = "^(\d+\s?)+$"
For Each r In Activesheet.UsedRange
If .Test(r.Value) Then r.Value = Val(r.Value)
Next
End With
End Sub
--------------------


SamanthaK Wrote:
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458
but
want to convert it to 100022458. I do not want text like: 'Market
value' to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=552144