Searching for asterisk

[wigi]

Hi all

This code searches for an asterisk in a range. In effect, it replaces cells with 0-5* to 0-5. All goes well, except if there's only 1 time a 0-5* and that instance is in the first cell of the range.

Set foundcell = rngStanden.Find("~*", LookIn:=xlValues, lookat:=xlPart)
        If Not foundcell Is Nothing Then
            firstAddress = foundcell.Address
            Do
                foundcell.Value = "0-5"
                Set foundcell = rngStanden.FindNext(foundcell)
            Loop While Not foundcell Is Nothing And foundcell.Address <> firstAddress   'here's the error
        End If

Does anyone know how to correct it?

Thanks in advance

Wigi

 

[Richard Schollar]

What if you include:

On Error Resume Next

...Rest of code...

Richard

 

[wigi]

What if you include:

On Error Resume Next

...Rest of code...

Richard

This works!

Thank you Richard!

 

[erik.van.geit]

Hi,

yes, this works ...

it is good practice to avoid "on error" if possible ...
let's check this out:
you are finding items, and changing them, so when the last item is changed, nothing is found anymore: this means foundcell will be nothing
then foundcell.Address will generate an error
solution: delete the underlined part
Loop While Not foundcell Is Nothing And foundcell.Address <> firstAddress
you only need that part for a "normal" find to avoid the endless loop

Set foundcell = rngStanden.Find("~*", LookIn:=xlValues, lookat:=xlPart)
        If Not foundcell Is Nothing Then
            Do
                foundcell.Value = "0-5"
                Set foundcell = rngStanden.FindNext(foundcell)
                MsgBox foundcell Is Nothing
            Loop While Not foundcell Is Nothing And foundcell.Address <> firstAddress
        End If

I found your statement strange

All goes well, except if there's only 1 time a 0-5* and that instance is in the first cell of the range.

for me it is not working anyway

hope this can clarify things

kind regards,
Erik

(tomorrow morning going to Antwerp (Mortsel) )

 

[Peter_SSs]

I am not sure exactly what is required here but would this be any good?
(Not sure whether cells might contain, say, 230-5* and what result would be required if thay did contain values like this. May want to change xlWhole to xlPart?)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Replace()
    <SPAN style="color:#00007F">With</SPAN> Range("A1:D20")
        .Replace What:="0-5~*", Replacement:="0-5", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, _
        ReplaceFormat:=False
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[wigi]

Hi Peter, Erik and others

The question is about soccer matches which end in 0-5 or 5-0 (one team is punished) or 5-5 (not a draw, the two teams have been punished; both lose 5-0). I keep track of these results (which are different from the normal 5-0, 0-5, 5-5 by using an asterisk.

I was thinking about doing it the way Peter suggested. Multiple options stand out:

- a Replace method for *5-0, 0-5* and *5-5*
- a Find method for the *, then with a Select case structure seeing which one it is
- looping through the range and then the same Select case structure

What would you suggest? The range is 50 cells at maximum.

Wigi

 

[erik.van.geit]

REPLACE is the inbuilt function to "replace": nothing else would be as good

 

[Peter_SSs]

Yes, I'm with Erik on this one. You just need 3 'Replaces' in the body of the code. Something like this though I am not certain of the 'Replacement' values in the sample below.

.Replace What:="0-5~*", Replacement:="0-5", ...
.Replace What:="~*5-0", Replacement:="5-0", ...
.Replace What:="~*5-5~*", Replacement:="5-5", ...

 

[wigi]

Thank you both!