VBA and superscript

[Helene]

I do some work for a company that abbreviates its own name with two capital
letters and a superscript "2". Usually when they type this abbreviation in
their own documents they use that special character that appears to be a
superscript 2 but is not a 2 superscripted, if you follow that.

When they send me documents, the superscript two shows up as an underscore
character.

I use Office 2001. In Word I can replace all the underscores with a two and
then select the two and superscript it with the following macro assigned to
a keystroke:

Sub newmacro()
Selection.Font.Superscript = True
End Sub


I don't know very much VBA so I'm sure there is probably an easier way to do
this, but this works for me.

I would like to do something similar in Excel, but when I use that macro it
superscripts the entire cell. I have tried recording a macro directly in
Excel to do this too, and then manually tweaking it so it will work in any
cell.

This is the best I have been able to do:

Sub newmacro()
ActiveCell.Characters(Start:=3, Length:=1).Caption = "2"
ActiveCell.Characters(Start:=3, Length:=0).Font.Superscript = True
ActiveCell.Characters(Start:=4, Length:=0).Font.Superscript = False
End Sub


but the only problem is that it only works when the abbreviation is the
first text in any cell. If it is the second or third word, etc. this doesn't
work.

Is there any way to select the one character I want to change and then run a
macro to make it a superscript two?

Anyone have any ideas?

 

[Bernard Rey]

Helene wrote :

I do some work for a company that abbreviates its own name with two capital
letters and a superscript "2". Usually when they type this abbreviation in
their own documents they use that special character that appears to be a
superscript 2 but is not a 2 superscripted, if you follow that.

When they send me documents, the superscript two shows up as an underscore
character.

I use Office 2001. In Word I can replace all the underscores with a two and
then select the two and superscript it with the following macro assigned to
a keystroke: .../...

Here are a couple of lines that will superscript the third character in the
active cell:

Sub SuperScripting()
ActiveCell.Characters(Start:=3, Length:=1).Font.Superscript = True
End Sub

And here's a way to enter "XX2" (to be adapted, of course) in a cell and
superscript it's third character;

Sub SuperScripting2()
With ActiveCell
.Formula = "XX2" ' adapt it to your comp. name
.Characters(Start:=3, Length:=1).Font.Superscript = True
End With
End Sub

 

[Helene]

Bernard Rey wrote on 6/12/04 3:19 AM:

Here are a couple of lines that will superscript the third character in the
active cell:

Thanks.

I think what I already had would consistently superscript the third
character of the active cell.

If the abbreviation is WR2 with the two superscripted, what I want to be
able to do is superscript the two if the WR2 comes at the beginning of the
cell (I already can do that) and also if it is in the middle of the cell. It
is almost never the case that "WR2" is the entire contents of any cell.

So say one cell contains "WR2 Sales" -- when I open it with my mac it
appears as if it is "WR_ Sales" because they used that special character
from the Windows character set. I can change that with the macro I have.

Sub newmacro()
ActiveCell.Characters(Start:=3, Length:=1).Caption = "2"
ActiveCell.Characters(Start:=3, Length:=0).Font.Superscript = True
ActiveCell.Characters(Start:=4, Length:=0).Font.Superscript = False
End Sub

But another cell might say "Total WR2 Sales" -- I'd ideally like a macro
that would change both of these with the same keystroke or toolbar button
when the cell is active. I get these spreadsheets from other people and I
just don't want to take the time to change all instances of WR_ with the
mouse when I receive them.

It seems like there should be some way to search within the active cell for
the pattern "WR2" to calculate the location of the "2" within the cell to
use for the Characters(Start:=X, Length:=1) portion of the code to make this
work.

 

[Helene]

It seems like there should be some way to search within the active cell for
the pattern "WR2" to calculate the location of the "2" within the cell to
use for the Characters(Start:=X, Length:=1) portion of the code to make this
work.

I think I mostly figured it out.

The following seems to work:

Sub changeWR_()
Dim WR_pos
WR_pos=InStr(ActiveCell.FormulaR1C1,"WR")+2
ActiveCell.Characters(Start:=WR_pos,Length:=1).Caption="2"
ActiveCell.Characters(Start:=WR_pos,Length:=1).Font.Superscript=True
ActiveCell.Characters(Start:=WR_pos+1,Length:=0).Font.Superscript=False
End Sub


Now I only have to figure out what to add to this so that it works when
there is more than one "WR_" in a single cell.

 

[JE McGimpsey]

It seems like there should be some way to search within the active cell for
the pattern "WR2" to calculate the location of the "2" within the cell to
use for the Characters(Start:=X, Length:=1) portion of the code to make this
work.

One way:

Public Sub Superscript2()
Dim nPos As Long
Dim sText As String
With ActiveCell
sText = .Text
nPos = InStr(1, sText, Chr(95), 1)
If nPos > 0 Then
Mid(sText, nPos, 1) = "2"
.Value = sText
.Characters(nPos, 1).Font.Superscript = True
End If
End With
End Sub

 

[JE McGimpsey]

Now I only have to figure out what to add to this so that it works when
there is more than one "WR_" in a single cell.

Try:

Public Sub Superscript2()
Dim nPos As Long
Dim sText As String
With ActiveCell
sText = .Text
nPos = InStr(1, sText, Chr(95), 1)
Do While nPos > 0
Mid(sText, nPos, 1) = "2"
.Value = sText
.Characters(nPos, 1).Font.Superscript = True
nPos = InStr(nPos, sText, Chr(95), 1)
Loop
End With
End Sub

 

[Helene]

Try:

Public Sub Superscript2()
Dim nPos As Long
Dim sText As String
With ActiveCell
sText = .Text
nPos = InStr(1, sText, Chr(95), 1)
Do While nPos > 0
Mid(sText, nPos, 1) = "2"
.Value = sText
.Characters(nPos, 1).Font.Superscript = True
nPos = InStr(nPos, sText, Chr(95), 1)
Loop
End With
End Sub

Thanks!! That didn't quite work, but knowing that funny character is Chr(95)
helps alot.

I think what the above code did was to replace the Chr(95) with twos
consistently, but when it finished running only the last two was
superscripted. Maybe replacing the whole .Value every loop somehow negated
the effect of superscripting in previous loops?

I changed it to use the caption property instead of .Value so it would only
be changing one character in the ActiveCell at a time and it works!!

Public Sub changeWR_()
Dim nPos As Long
With ActiveCell
nPos = InStr(1, .Text, Chr(95), 1)
Do While nPos > 0
.Characters(nPos, 1).Caption = "2"
.Characters(nPos, 1).Font.Superscript = True
nPos = InStr(nPos, .Text, Chr(95), 1)
Loop
End With
End Sub


Thank you JE & Bernard for helping me to figure this out. This will save me
alot of time.

Helene