hidden character in string

[Syscon]

I am serching for a string but the string seems to have an invisible control
character - probably Chr(13) or something of the like at the end. My script
is :

Range("A2").Select 'Set for start range
Do Until ActiveCell = ""
If ActiveCell = "Gobbledegook" Then 'Test to find
vText = ActiveCell.Offset(1, 0).Value 'Grab data
ActiveCell.Offset(1, 14) = vText ' Copy it
End If

With this hidden character I cannot use the search! How can I find the cell
ignoring the character(s)

Richard

 

[Norman Jones]

Hi Richard,

Perhaps, try something like:

'=============>>
Public Sub Tester()
If InStr(1, ActiveCell.Value, _
"Gobbledook", vbTextCompare) = 0 Then
'your code
End If
End Sub
'<<=============

 

[Norman Jones]

Hi Richard,

Additionally, if you wish to identify individual characters, you may wish to
consider Chip Pearson's Cell View add-in:

http://www.cpearson.com/Excel/CellView.htm

 

[Syscon]

The CellView add in has identified ( info - works in excell 2007 ) decimal
010 at the end of the text.
Is there anyway to strip this from all cells in Col. A to enable my search
to run?. I have a lot of individual Excel sheets to run my macro on!

Richard

 

[Norman Jones]

Hi Richard,

Try something like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
For Each SH In WB.Worksheets
SH.Columns("A").Replace _
What:=Chr(10), _
Replacement:=vbNullString, _
SearchOrder:=xlByRows, _
Lookat:=xlPart
Next SH
End Sub
'<<=============

 

[Syscon]

Thanks for the very fast reply !
And if I just want to strip it from the open worksheet? ( as some worksheets
have "memo" fields with a free form entrys that I do not want striped )
Presumably it is a change to :-
SH.Columns("A").Replace _

Richard

 

[Norman Jones]

Hi Richard,

Try:

'=============>>
Public Sub Tester()
Activesheet.Columns("A").Replace _
What:=Chr(10), _
Replacement:=vbNullString, _
SearchOrder:=xlByRows, _
Lookat:=xlPart
End Sub
'<<=============

 

[Syscon]

Norman,
Works a treat. Thank you for the very fast replies.