That's super, Rick. I "get" it now. :) They're not paying you enough.

Cliff


"Rick Rothstein" wrote:

See if this commented version of the code helps you out...

Sub ConvertSymbolsToValues()
' It's a good idea to always declare your variables
Dim X As Long
Dim C As Range
' The With statement is establishing the object that the Find and
' FindNext methods will operate on (the UsedRange of Sheet1). We
' do this to limit the search to an area where values are known to be
With Worksheets("Sheet1").UsedRange
' You have three symbols to replace, and their Unicode values are
' in sequential order, so we need to loop three times incrementing
' the loop by one each time
For X = 1 To 3
' The Hex values for your symbols are 255B, 255C, 255D... the
' equivalent converted Decimal values for these Hex values
' are 9563, 9564 and 9565 respectively. In order to produce the
' fractional values you want in the order you want them, we need
' to traverse the Decimal values in reverse order (9565 is 0.25,
' 9564 is 0.5 and 9563 is 0.75). We do that by subtracting 1, 2
' and 3 from 9566 to produce the value 9565, 9564 and 9563 in
' that order. So, each time through the loop, we will look for
' the Unicode character with code value of 9565, 9564 and 9563
' in that order
Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart)
' Test if the currently being looked for symbol was found
' anywhere within the UsedRange on Sheet1
If Not C Is Nothing Then
' If so, let's look for all of that current symbol within the
' UsedRange on Sheet1
Do
' We know from the If..Then test above that C is referencing
' one of the symbols we were searching for, so let's replace
' each occurrence of that symbol with its equivalent fraction
' (which we calculate by dividing the current loop index
' (which is 1, 2 or 3) by 4 to produce .25, .5, or .75
' depending on the value of the loop index
C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##"))
' Look for the current symbol elsewhere within the UsedRange
Set C = .FindNext(C)
' If C is not nothing, then we found another symbol somewhere,
' so loop to process it. If C is nothing, then there are no more
' instances of that current symbol, so drop out of the Do loop
' back into the For..Next loop to look for the next symbol
Loop While Not C Is Nothing
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"CliffG" wrote in message
...
Beautiful, perfect...thank you so much. :) If you have the time and are
inclined, could you give me a brief explaination of what is going on in
this
script? I have done some javascript and php (just enough for my own
purposes), so I can usually at least follow the logic in a routine. This
one
has me scratching my head. I don't see anything familar like variables
being
manipulated. Where do you get ChrW(9566 - X) for example. My curiosity is
overwhelming me.

Thanks again,

Cliff


"Rick Rothstein" wrote:

Okay, try this variation...

Sub ConvertSymbolsToValues()
Dim X As Long
Dim C As Range
With Worksheets("Sheet1").UsedRange
For X = 1 To 3
Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart)
If Not C Is Nothing Then
Do
C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4,
".##"))
Set C = .FindNext(C)
Loop While Not C Is Nothing
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"CliffG" wrote in message
...
Hey Rick,

Well, you were right..it works. Yippee Like I said, I'm not a coder,
so
I
don't know how (wish I did, I do like to follow the logic). Just two
issues
remain, the first I think you can fix, hopefully the second. The
values
.75
and .25 are switched when replaced in the spreadsheet. The .50 is
replaced
correctly.

The second issue is that the replaced values include a zero before the
decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many
of
the cells already contain a whole number, so for example, 8•œ becomes
80.50
instead of the desired 8.50.

Thanks for your effort, I really do appreciate it.

"Rick Rothstein" wrote:

You don't integrate it with what the macro recorder gave you... forget
what
the macro recorder gave you... delete it and copy/paste my macro in
its
place. Oh, and change the sheet name I used (Sheet6) in the With
statement
to the name of the worksheet where your data at... then, just run the
macro
and it should replace the three symbols you showed me (•›, •œ and •�)
with
the
numbers .25, .5 and .75 wherever they appear on the worksheet.

--
Rick (MVP - Excel)