wingdings characters and codes

[Larry Levinson]

Hi:
I have a column of 20 characters that I paste into a column in excel
and when I turn the font for that column into wingdings, the three
characters are a pair of scissors, a telephone and a candle. the
character codes -- according to Start|Programs|Accessories|System
Tools|Character Map|WingDings -- are:

Scissors = 0x22
Candle = 0x27
Phone = 0x28

I would like to determine the following:
1) the number of scissors
2) the number AND row of candles for further processing

How do I use the character codes in this vba script?

thanks in advance.

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Larry Levinson]

Word says the characters are also =

Scissors = 0x22 = windings 61474
Candle = 0x27 = windings 61479
Phone = 0x28 = windings 61480

but how do I use this data?

Hi:
I have a column of 20 characters that I paste into a column in excel
and when I turn the font for that column into wingdings, the three
characters are a pair of scissors, a telephone and a candle. the
character codes -- according to Start|Programs|Accessories|System
Tools|Character Map|WingDings -- are:

Scissors = 0x22
Candle = 0x27
Phone = 0x28

I would like to determine the following:
1) the number of scissors
2) the number AND row of candles for further processing

How do I use the character codes in this vba script?

thanks in advance.

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Larry Levinson]

Word says the characters are also =Scissors = 0x22 = windings 61474
Candle = 0x27 = windings 61479
Phone = 0x28 = windings 61480

and I can produce them in excel with ALT-KEYPAD 61474, or even ", (, '
and in wingdings font, but they do not match when I try to compare
them with what is in my source data column.

but how do I use this data?



Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Peter T]

Hi Larry,
I have a problem with your Candle unless you put two of them in a cell. It's
actually an apostrophe which has a special significance for Excel. Anyway
have a go with this:

Sub Tester11()
Dim cell As Range
Dim sAddr As String, vArr, sWhat As String

vArr = Array(&H22, &H27, &H28) 'or (34, 39, 40)

With Selection 'or eg Range("A1:B20")
For i = 0 To 2
sWhat = Chr(vArr(i))
vArr(i) = 0
sAddr = ""
Set cell = .Find(What:=sWhat, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
vArr(i) = vArr(i) + 1
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> sAddr
End If
Next
End With
MsgBox vArr(0) & vbCr & vArr(1) & vbCr & vArr(2)
End Sub

Regards,
Peter

 

[Larry Levinson]

Hmmmmmm ... what I get back is a message box with three zeros. The
thing is: I have found how to produce the characters ...
g1:g226 is the number 30 through 255, column h1:h226 has the formula
=char(g1) etc ... then, set the font on column H to wingdings.


Now, here is the problem ... =b5=h5 comes back as FALSE.

to the eye, they both look like scissors, but apparently there is
something essentially different about the actual contents ... thanks
for your help ...

Hi Larry,
I have a problem with your Candle unless you put two of them in a cell. It's
actually an apostrophe which has a special significance for Excel. Anyway
have a go with this:

Sub Tester11()
Dim cell As Range
Dim sAddr As String, vArr, sWhat As String

vArr = Array(&H22, &H27, &H28) 'or (34, 39, 40)

With Selection 'or eg Range("A1:B20")
For i = 0 To 2
sWhat = Chr(vArr(i))
vArr(i) = 0
sAddr = ""
Set cell = .Find(What:=sWhat, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
vArr(i) = vArr(i) + 1
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> sAddr
End If
Next
End With
MsgBox vArr(0) & vbCr & vArr(1) & vbCr & vArr(2)
End Sub

Regards,
Peter

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Peter T]

Hmmmmmm^³
Change
LookIn:=xlFormulas
to
LookIn:=xlValues

Regards,
Peter

 

[Larry Levinson]

thanks for your help, but I think I am going to have to do this
another way BECAUSE ... i tried your syntax on a column of wingding
characters that I created with ALT-61474 etc ... and it came up with
one for the candle, which is correct. ran the same text on my column
of 20 that I copied/pasted from another source and then set the font
for that column to wingdings and it failed to find anything.
THEREFORE, I believe that it only LOOKS like those symbols, but its
either really something else or within some kind of container that we
don't seem to be able to pierce.

Hmmmmmm^³
Change
LookIn:=xlFormulas
to
LookIn:=xlValues

Regards,
Peter

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Peter T]

Try the simple way, enter your characters as:
a double quote ", an apostrophe (but do twice), and an open bracket "(".
or alt 0034, Alt 0039, Alt 0040

Select cells containing the above and try my code again. The Font Wingdings
or whatever is not relevant (except the candle / apostrophe issue).

Regards,
Peter

 

[Larry Levinson]

right, i am sure the code would work on the characters created that
way. My problem is that I need to be able to work on a group of
characters from another source that I copy (click and drag) and paste
into Excel.

Its from that list of 20 characters that I want to be able to count
the number of scissors, the number of candles, and the rows in which
the candles appear. Unfortunately, I do not have the luxury of being
able to create the characters myself; I need to read it from the list
I generate from my source.

While they APPEAR to be scissors, phones and candles, apparently there
is something else in the cell that we can't tease out. If you really
want to see it, send me your email address and I will send you a copy
of my Excel sheet.

Try the simple way, enter your characters as:
a double quote ", an apostrophe (but do twice), and an open bracket "(".
or alt 0034, Alt 0039, Alt 0040

Select cells containing the above and try my code again. The Font Wingdings
or whatever is not relevant (except the candle / apostrophe issue).

Regards,
Peter

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Peter T]

You gave a clue with "drag copy". In Office97 I dragged cells containg
single Winding characters from a Word table into Excel. Some, but not all
character codes appear to have changed, odd! But doing same in Office2000
and everything seems normal, and my code works as expected.

I might have been a bit hasty about saying the Wingdings font was not
related. I'll try and have another look tomorrow, unless (hopefully!)
someone else has sorted you out.

Regards,
Peter

 

[Larry Levinson]

You, sir, to my undying gratitude, are my only correspondent to this
point.

You gave a clue with "drag copy". In Office97 I dragged cells containg
single Winding characters from a Word table into Excel. Some, but not all
character codes appear to have changed, odd! But doing same in Office2000
and everything seems normal, and my code works as expected.

I might have been a bit hasty about saying the Wingdings font was not
related. I'll try and have another look tomorrow, unless (hopefully!)
someone else has sorted you out.

Regards,
Peter

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Peter T]

After your latest how can I not come back!

Seems like one of two possibilities or a combination is at issue.

1. In my Office97 bringing certain Windings (0034 & 0039 and others) into
Excel get converted to display as squares. Checking their codes all return
63 which is a question mark (not displayed as such). Something special with
picture fonts perhaps. But in my Office2000 all OK. (Might be difficult for
others to replicate the problem.)
or
2 Several characters are regarded as special for XL. I've already mentioned
the single quote but also a double quote and maybe a bracket (scissors,
candle & phone). If used in formulas could cause problems, maybe XL is
trying to pre-empt.

Try this:
In a backup of your source file, replace your characters by typing A B & C
in cap's respectively (3 different ok hands - right?). Repeat your drag copy
into XL. Individually select cells, press F2 edit and look in the edit box =
should see A, B, & C - right?

In the code change
vArr = Array(&H22, &H27, &H28) 'or (34, 39, 40)
to
vArr = Array(65, 66, 67)' codes for A, B & C

With the previous xlFormulas > xlValues amendment, run the code. If this
works I don't know what to do as regards the other characters.

Regards,
Peter