Searching for asterisk

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
What if you include:

Code:
On Error Resume Next

...Rest of code...

Richard

This works!

Thank you Richard!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Thank you both!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,920
Messages
5,545,029
Members
410,647
Latest member
bernardazar
Top