Finding spaces

[AlexanderBB]

I'm using Find like so and all is working well

Set Found = Range(rCol).Find(What:=TextBox1.Text, lookIn:=xlComments, Lookat:=xlPart)

Except where the text in Comments may have chr$160) in place of chr$(32)
This was used as a delimiter to identify parts1 and 2 in a string.
Can I modify find somehow so it will still find these instances.
textbox1 has no way of knowing.

 

[Alex Blakenburg]

I would have thought every comment (note) would contain a space (chr 32) character.
If you want to use the TextBox and you have a numeric keypad typing Alt+0160 into the text box should let you use it in your find command.

 

[AlexanderBB]

Yes there are space characters in the comments but one acts as a delimiter.
The text box input doesn't know where this and consequently Found becomes nothing.
What I was wondering was is there any way to have e.g.
= Range(rCol).Find(What:=Replace(TextBox1.Text," ","*"), lookIn:=xlComments, Lookat:=xlPart)
I also tried "?" which Excel is aware of (so I read) but looks like that method can;t use wildcards.

 

[Alex Blakenburg]

You might need to give us an example of what is in the comments box and what you want to find. xlPart is effectively already using wildcards.

 

[AlexanderBB]

Sure thing
The Comment is 'The Movie Ben Hur'.. The last space is chr$(160).
The text box has "Ben Hur" with the space being chr$(32)
I thought this would be pretty straightforward but unless I've missed something I had to do it in two passes "Ben" and "Hur"

 

[Alex Blakenburg]

See if something like this works for you.

Sub LookInComments()

Dim Look As Long
Dim SearchPart As Long
Dim rCol As Range
Dim Found As Range, FirstFound As Range
Dim strFind As String
Dim hasSpace As Boolean

Look = xlComments
SearchPart = xlPart
Set rCol = Range("C2:C58")

strFind = TextBox1.Text

hasSpace = False
If Right(strFind, 1) = " " Then hasSpace = True

Set Found = rCol.Find(What:=Trim(strFind), LookIn:=Look, LookAt:=SearchPart)
        
If Not Found Is Nothing Then
    Set FirstFound = Found
    Do
        If hasSpace Then
            If InStr(1, Replace(Found.Comment.Text, ChrW(160), " "), strFind, vbTextCompare) > 0 Then Debug.Print "With space", Found, Found.Address
        Else
            Debug.Print "No Space"; Found, Found.Address
        End If
        
        

        Set Found = rCol.FindNext(After:=Found)
    Loop Until FirstFound.Address = Found.Address Or Intersect(rCol, Found) Is Nothing
    
End If
End Sub

 

[AlexanderBB]

Sorry I didn't follow the logic on that.
> If Right(strFind, 1) = " " Then hasSpace = True
If there's no trailing space hasspace will always be false
>Set Found = rCol.Find(What:=Trim(strFind), LookIn:=Look, LookAt:=SearchPart)
Won't Found always be nothing unless strfind has a matching chr$(160) ?

 

[Alex Blakenburg]

I am not sure of your exact requirements and we know we don't want to loop through twice.
So the assumption is that you want to option to add a space in the text box but that you may also want to search without the space.
So the first "If statement" determines which of the 2 cases applies.

If you have a space at the end you want to find both: the "search string + space" AND "search string + chr 160".
So if hasSpace = True
• Used Find to find the cell containing the Search String without the Trailing space (Trim it off)
• Once found, read the cell's comment and convert the chr160 to a space (via replace) ie normalise the comment text to be tested.
• Now test the normalised comment text to see if "the found cell" meets the criteria with the space at the end as per the Textbox.

 

[AlexanderBB]

There isn't a trailing space. Msg5 has an example.
The search is inside comment.text for a string which may contain chr$(160) instead of chr$(32).

 

[Alex Blakenburg]

If you are saying that you want to search for both "Ben" & Space OR Chr160 & "Hur" then using Find is probably not the best way to go about it.
It will probably be easier to just loop through the range.
(assuming you don't have a huge amount of data with only a small number of occurrences of the search string).