Test condition never satisfied in loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is wrong with the loop condition in this code?

The purpose of the code is to remove all the spaces before the first
character in a column of selected worksheet cells like this:

F
1
R
H
T
U
N
25.05
8.80
35.38
27.50
24.25
26.00
27.50
22.88

where each row is a value in a cell of a column in the worksheet.

When I run the code on these cell values, the test condition, testStr =
Left(nxtChar, 1), is never satisified.

Thank you.

John Wirt

Public Sub RemoveSpace()
Dim nxtChar As String, testStr As String
Dim rngTxt As Range
Dim rw As Range

testStr = Left(" ", 1)
Set rngTxt = Selection
For Each rw In rngTxt.Rows
nxtChar = rw.Cells(1, 1).Value
Do
nxtChar = Mid(nxtChar, 2, Len(nxtChar) - 1)
Loop While testStr = Left(nxtChar, 1)
rw.Value = nxtChar
Next
End Sub
 
Can't you just use Trim (or LTrim)

Public Sub RemoveSpace()
Dim rngTxt As Range

For Each rngTxt In Selection
rngTxt.Value = Trim(rngTxt.Value)
'Or
'rngTxt.Value = LTrim(rngTxt.Value)
Next
End Sub

NickHK
 
Trim() and your code is better but the improvement still does not work.

When I apply the code to the original example below, it does not work. No
spaces are removed from the beginning or the end of each text string.

The problem has something to do with what is pasted into Excel from the
original source output of the example. The source is text output from a
statistical program that is written to a .txt file. Actually, the output is
written to a Word file which is then saved as a .txt file but this should be
the same thing, no?

If I take the very same source output and manually replace all of the spaces
before and after each text string, then the code works.

There is something about how Excel formats text that is pasted into a
spreadsheet from another source that is causing the problem.

In order to get the output in, I have to convert the originally fixed width
formatted text to columns using the converter built into Excel.

It could be a problem with how Excel deals with a text string consisting of
spaces and numbers pasted into a worksheet.

Anyway, this is a bit puzzling and a source of difficulty becuase hand
editing all the data I need to deal with will take a lot of time.

John
 
Are you sure they actually are spaces (ASCII 32) and not some other form of
white space ?

If you enter this nearby, pointing to the relevant cell, what does it return
?
=CODE(LEFT(A1,1))

NickHK
 
There is something about how Excel formats text that is pasted into a
spreadsheet from another source that is causing the problem.
Click to expand...

Hi. This small program is probably the one I use the most...
Select the range to "Fix" first.

Sub FixRange()
Dim Rng As Range
Dim Cell As Range
Set Rng = Selection
With WorksheetFunction
For Each Cell In Rng.Cells
Cell = Trim(Replace(.Clean(Cell.Value), Chr(160), vbNullString))
Next Cell
End With
End Sub
 
Dana,
This is awesome. It works. It also clearns the spaces off the cells "values."
Where do all of the stray "codes" come from.
Now there is one more person who uses this code.
Thanks.
John Wirt
 
There are odd codes in the "texts" in the cells in addition to the expected
alphnumeric codes. Here is an example. I've used the MID() function to pick
out the characters one by one.

First here is are the original "texts" (the data).

F
2
H
S
C
P
D
R

200406
200406
200210
200405
200405
200405
200405
200406
200407
200405
200406
200406
200305
200306
200408
200407

Then, here is the result of using CODE and MID to look at each character.

160 160 160 160 160 160 160 50 160 160 #VALUE!
160 160 160 160 160 160 32 72 160 160 #VALUE!
160 160 160 160 160 160 32 83 160 160 #VALUE!
160 160 160 160 160 160 32 67 160 160 #VALUE!
160 160 160 160 160 160 32 80 160 160 #VALUE!
160 160 160 160 160 160 32 68 160 160 #VALUE!
160 160 160 160 160 160 32 82 160 160 #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE!
160 160 32 32 50 48 48 52 48 54 #VALUE!
160 160 32 32 50 48 48 52 48 54 #VALUE!
160 160 32 32 50 48 48 50 49 48 #VALUE!
160 160 32 32 50 48 48 52 48 53 #VALUE!
160 160 32 32 50 48 48 52 48 53 #VALUE!
160 160 32 32 50 48 48 52 48 53 #VALUE!
160 160 32 32 50 48 48 52 48 53 #VALUE!
160 160 32 32 50 48 48 52 48 54 #VALUE!
160 160 32 32 50 48 48 52 48 55 #VALUE!
160 160 32 32 50 48 48 52 48 53 #VALUE!
160 160 32 32 50 48 48 52 48 54 #VALUE!
160 160 32 32 50 48 48 52 48 54 #VALUE!
160 160 32 32 50 48 48 51 48 53 #VALUE!
160 160 32 32 50 48 48 51 48 54 #VALUE!
160 160 32 32 50 48 48 52 48 56 #VALUE!
160 160 32 32 50 48 48 52 48 55 #VALUE!


What are the "160s" at the beginning of each string? For the text strings,
there are lot of them. For the numbers, there are two.

Thank you.

John Wirt
 
Plesae see my response to Nick above. Thanks for your help.

What are the "160s" in the original text strings?

Thank you.

John
 
What are the "160s" in the original text strings?

Hi. Glad it's working for you. :>)

I believe the 160's are Nonbreaking spaces (&nbsp).

Well, I was just now debating on whether to remove the Clean function.
It's been a while, so I just decided to read the Help on Clean in Excel
2007.
Excel 2007 has a little more to say on the subject.

It actually now mentions the '160' problem. However, it also has this to
say.

....The CLEAN function was designed to remove the first 32 non-printing
characters in the 7 bit ASCII code (values 0 through 31) from text. In the
Unicode character set, there are additional nonprinting characters (values
127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not
remove these additional nonprinting characters.

So, it looks like we should modify the code now to remove these additional
characters also.
I did a quick search on these additional characters, but didn't find any
documentation.
It's never been a problem before, but since Microsoft actually mentions it
in help, perhaps the following...

Sub FixRange()
'// See Help on worksheet function "Clean"
Dim Rng As Range
Dim Cell As Range
Dim s As String

Set Rng = Selection
With WorksheetFunction
For Each Cell In Rng.Cells
s = .Clean(Cell.Value)
s = Replace(s, Chr(160), vbNullString) 'NonBreaking Space
s = Replace(s, Chr(127), vbNullString) '?
s = Replace(s, Chr(129), vbNullString) '?
s = Replace(s, Chr(141), vbNullString) '?
s = Replace(s, Chr(143), vbNullString) '?
s = Replace(s, Chr(144), vbNullString) '?
s = Replace(s, Chr(157), vbNullString) '?
Cell = Trim(s)
Next Cell
End With
End Sub
 
Back
Top