My "Do While...Loop" wont loop and delete the spaces between my digits.

I am trying to eliminate the spaces between digits in Australian
Business Numbers which have the format "NN NNN NNN NNN" to produce an
eleven digit number with no spaces. I have written a function to do
this that appears to work. I now want to call this function from a
subroutine to change about 3000 numbers. The subroutine is causing me
headaches. Here is my space-eliminating function:

Function RemoveSpaces(CellContents As String) As String
Do While InStr(1, CellContents, " ") > 0
CellContents = Replace(CellContents, " ", "")
Loop
RemoveSpaces = CellContents
End Function

Here is my subroutine:

Sub abnTidy2()
Dim CellContents As String

Sheets("Scratch").Select
Range("E2").Select 'which contains the number 17 000 032 128
CellContents = Selection.Value
Do While CellContents <> Null
Call RemoveSpaces(CellContents)
Selection.Offset(1, 0).Select
CellContents = Selection.Value
Loop
End Sub

Cell "E2" contains the number "17 000 032 128".

Using the debugger, I step all the way to "Do While CellContents <>
Null", and the next time I "step into" it jumps to "End Sub". Now, my
"Locals" window confirms that ell "E2" contains the string "17 000 032
128", so it isn't null. So, if CellContents is not Null, the debugger
should step to the function call "Call RemoveSpaces(CellContents)". It
doesn't do this and I can't figure out why.

Can anybody help, please? Also, criticisms / comments / style tips
regarding my code would be warmly appreciated.

Update on above. I got my subprocedure to loop by changing it to a
"Do...Loop Until" loop(although I'm not sure it will stop looping for
the same reason that the "Do While...Loop" wouldn't start) but now my
space-eliminating function isn't eliminating spaces. It was the other
day. I don't know what has changed. Here is my "Do...Loop Until"
subprocedure:

Sub abnTidy2()
Dim CellContents As String

Sheets("Scratch").Select
Range("E2").Select
CellContents = Selection.Value
Do
Call RemoveSpaces(CellContents)
Selection.Offset(1, 0).Select
CellContents = Selection.Value
Loop Until CellContents = Null
End Sub

Can debuggers debug Functions without having to write a subprocedure to
call them? I tried to debug my "Function RemoveSpaces(CellContents As
String) As String" function from within it ("step into") and the cursor
just sat there and blinked at me.

I'm sorry about all the stupid questions. I've just started coding a
project again after a break of a few years and I need to work up some
momentum. Every time I think something is obvious, it isn't. When I do
get things working, they sometimes stop working. You need to do this
stuff all day, every day, to get confidence, which I don't yet have.
However, it appears that I will be doing this for some time to come.

Change

Do While CellContents <> Null

with

Do While Len(CellContents) > 0

even there is nothing in the cell, .Value property returns zero-length
string.


Kind regards.

Haldun Alay



"Cloudfall" <SydneyCloudfall@hotmail.com>, haber iletisinde sunlari
yazdi:1125381076.956897.104060@z14g2000cwz.googlegroups.com...
>I am trying to eliminate the spaces between digits in Australian
> Business Numbers which have the format "NN NNN NNN NNN" to produce an
> eleven digit number with no spaces. I have written a function to do
> this that appears to work. I now want to call this function from a
> subroutine to change about 3000 numbers. The subroutine is causing me
> headaches. Here is my space-eliminating function:
>
> Function RemoveSpaces(CellContents As String) As String
> Do While InStr(1, CellContents, " ") > 0
> CellContents = Replace(CellContents, " ", "")
> Loop
> RemoveSpaces = CellContents
> End Function
>
> Here is my subroutine:
>
> Sub abnTidy2()
> Dim CellContents As String
>
> Sheets("Scratch").Select
> Range("E2").Select 'which contains the number 17 000 032 128
> CellContents = Selection.Value
> Do While CellContents <> Null
> Call RemoveSpaces(CellContents)
> Selection.Offset(1, 0).Select
> CellContents = Selection.Value
> Loop
> End Sub
>
> Cell "E2" contains the number "17 000 032 128".
>
> Using the debugger, I step all the way to "Do While CellContents <>
> Null", and the next time I "step into" it jumps to "End Sub". Now, my
> "Locals" window confirms that ell "E2" contains the string "17 000 032
> 128", so it isn't null. So, if CellContents is not Null, the debugger
> should step to the function call "Call RemoveSpaces(CellContents)". It
> doesn't do this and I can't figure out why.
>
> Can anybody help, please? Also, criticisms / comments / style tips
> regarding my code would be warmly appreciated.
>

Haldun, thank you, it worked. I also worked out all my other problems.

Hi

There was no need for any code at all! You had to select the range with
phone numbers, select from Edit menu 'Replace', define a single space (' ',
without quotes) in 'Search for' field, leaving 'Replace with' field empty,
and click on 'Replace All' button. And it would be done.

When some numbers started with 0's, and you did want to preserve them, then
or you had to format the range as Text at start, or at end you had to format
the range (where all entries were converted to numbers, except the cell
format was text before) as Custom "00000000000".

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message
news:1125381076.956897.104060@z14g2000cwz.googlegroups.com...
>I am trying to eliminate the spaces between digits in Australian
> Business Numbers which have the format "NN NNN NNN NNN" to produce an
> eleven digit number with no spaces. I have written a function to do
> this that appears to work. I now want to call this function from a
> subroutine to change about 3000 numbers. The subroutine is causing me
> headaches. Here is my space-eliminating function:
>
> Function RemoveSpaces(CellContents As String) As String
> Do While InStr(1, CellContents, " ") > 0
> CellContents = Replace(CellContents, " ", "")
> Loop
> RemoveSpaces = CellContents
> End Function
>
> Here is my subroutine:
>
> Sub abnTidy2()
> Dim CellContents As String
>
> Sheets("Scratch").Select
> Range("E2").Select 'which contains the number 17 000 032 128
> CellContents = Selection.Value
> Do While CellContents <> Null
> Call RemoveSpaces(CellContents)
> Selection.Offset(1, 0).Select
> CellContents = Selection.Value
> Loop
> End Sub
>
> Cell "E2" contains the number "17 000 032 128".
>
> Using the debugger, I step all the way to "Do While CellContents <>
> Null", and the next time I "step into" it jumps to "End Sub". Now, my
> "Locals" window confirms that ell "E2" contains the string "17 000 032
> 128", so it isn't null. So, if CellContents is not Null, the debugger
> should step to the function call "Call RemoveSpaces(CellContents)". It
> doesn't do this and I can't figure out why.
>
> Can anybody help, please? Also, criticisms / comments / style tips
> regarding my code would be warmly appreciated.
>

Hi Arvi,

Thanks for your suggestion. I implemented your recommendation. It
executes much faster and just is a much better implementation.

Regards,

Terry R.