PDA

View Full Version : How to send text that contain subscript and superscript from a macro to a cell?



cgphung
03-06-2008, 10:55 PM
I am writing a macro that will generate chemical formula containing subscripts and superscripts (e.g. 'H2O' - where '2' is a subscript or 'S-2' - where '-2' are superscripts). Is there anyway I can accomplish this?

Please Help!!!

CGP

thomaspatton
03-07-2008, 05:22 AM
Public Sub SubScriptEnter()
With Worksheets("Sheet1").Range("c1")
.Value = "Chem2 on Route66"
.Characters(5, 1).Font.Subscript = True
.Characters(15, 2).Font.Subscript = True
End With
End Sub

Main variable is the ".Character(5,1)" portion, where the first number is equal to the count of the letter you want subscripted and the second number being the number of letters after that to subscript.

If you run this code, for example, it will only Subscript the "2" and the "66".

Charlize
03-07-2008, 07:26 AM
I am writing a macro that will generate chemical formula containing subscripts and superscripts (e.g. 'H2O' - where '2' is a subscript or 'S-2' - where '-2' are superscripts). Is there anyway I can accomplish this?

Please Help!!!

CGPRun this macro when you are standing on a cell with a chemical formula in it like : H2OO-2
Maybe this one will get you started.Sub chemical()
Dim item As Variant
Dim vloop As Long
Dim anumber() As String
ReDim anumber(Len(ActiveCell.Value))
For vloop = 0 To Len(ActiveCell.Value) - 1
anumber(vloop) = Mid(ActiveCell.Value, vloop + 1, 1)
If anumber(vloop) = "-" Then
ActiveCell.Characters(vloop + 2, 1).Font.Subscript = True
vloop = vloop + 2
ElseIf IsNumeric(anumber(vloop)) Then
For Each item In Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
If anumber(vloop) = item Then
ActiveCell.Characters(vloop + 1, 1).Font.Superscript = True
Exit For
End If
Next item
End If
Next vloop
For vloop = Len(ActiveCell.Value) To 1 Step -1
If Mid(ActiveCell.Value, vloop, 1) = "-" Then
ActiveCell.Characters(vloop, 1).Delete
End If
Next vloop
End SubCharlize

ps.: Maybe we don't need the looping for the numbers. Since we already did a check to see if it was numeric.

cgphung
03-07-2008, 11:47 AM
Thanks for those had shown me the way. This is what I had come up with and solved my problem. Thanks!

Public Sub Chemical()
For x = 1 To Len(ActiveCell.Value)
char = Mid(ActiveCell.Value, x, 1)
' Check for positive or negative charge for superscript
If char = "+" Or char = "-" Then
If IsNumeric(Mid(ActiveCell.Value, x + 1, 1)) Then
ActiveCell.Characters(Start:=x, Length:=2).Font.Superscript = True
x = x + 1
Else
ActiveCell.Characters(Start:=x, Length:=1).Font.Superscript = True
End If
End If
' Check for numeric for subscript
If IsNumeric(char) Then
ActiveCell.Characters(Start:=x, Length:=1).Font.Subscript = True
End If
Next x
End Sub

CGP
:thumb