does anyone know how to encode subscripts?

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,553
I need to write things like H2O in the Excel but 2 of course being subcripted. Does anyone know how to code for that in VBA?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Like this?

Code:
Sub Water()
    With ActiveCell
        .Value = "H2O"
        .Characters(Start:=2, Length:=1).Font.Subscript = True
    End With
End Sub

Note that the macro recorder would have given you the VBA code.
 
Upvote 0
Like this?

Code:
Sub Water()
    With ActiveCell
        .Value = "H2O"
        .Characters(Start:=2, Length:=1).Font.Subscript = True
    End With
End Sub

Note that the macro recorder would have given you the VBA code.

I'm still having trouble with this. I'm trying to come up with some code that will simply subscript whatever is between /\ and will then eliminate the /\. E.g. I want to convert EA/1\b to EA1b, 1 being subscripted.
 
Upvote 0
If you have a lot of subscript items, you might need to come up with a lookup table of sorts and loop through it. E.G. Symbol/Subscript Character(s)/Start/Length, and then substitute those as variables in the code.
 
Upvote 0
Can you build a list of them? If so, then you would use a second column to identify which character is to be subscripted and then have your code look for the value in the list.
 
Upvote 0
Maybe ...

Code:
Sub SubSc()
    ' subscripts characters bracketed by /\
    Dim rFind       As Range
    Dim sAddr       As String
    Dim sInp        As String

    Dim ab()        As Boolean
    Dim b           As Boolean

    Dim sOut        As String
    Dim iRd         As Long
    Dim iWr         As Long

    Set rFind = Cells.Find(What:="/*\")
    If Not rFind Is Nothing Then
        sAddr = rFind.Address

        Do
            With rFind
                sInp = .Text
                If Balanced(sInp) Then
                    iWr = 0
                    sOut = Replace(Replace(sInp, "/", ""), "\", "")
                    ReDim ab(1 To Len(sOut))

                    For iRd = 1 To Len(sInp)
                        Select Case Mid(sInp, iRd, 1)
                            Case "/"
                                b = True
                            Case "\"
                                b = False
                            Case Else
                                iWr = iWr + 1
                                ab(iWr) = b
                        End Select
                    Next iRd
                Else
                    .Interior.Color = vbYellow
                End If

                .Value = sOut
                
                For iWr = 1 To Len(sOut)
                    If ab(iWr) Then .Characters(iWr, 1).Font.Subscript = True
                Next iWr
            End With

            Set rFind = Cells.FindNext(rFind)
            If rFind Is Nothing Then Exit Do
        Loop While rFind.Address <> sAddr
    End If
End Sub

Function Balanced(sInp As String) As Boolean
    ' returns True is sInp has balanced "/\"
    ' excercise left to user

    Balanced = True
End Function
 
Last edited:
Upvote 0
Revised for generality ...
Code:
Sub SubScr()
    ' subscripts characters bracketed by open and closing braces
    ' ignores nesting
    
    Const sOpn As String * 1 = "/"
    Const sCls As String * 1 = "\"
    
    Dim rFind       As Range
    Dim sAddr       As String
    Dim sInp        As String

    Dim ab()        As Boolean
    Dim b           As Boolean

    Dim sOut        As String
    Dim iRd         As Long
    Dim iWr         As Long

    Set rFind = Cells.Find(What:=sOpn, LookIn:=xlValues, LookAt:=xlPart)
    If Not rFind Is Nothing Then
        sAddr = rFind.Address

        Do
            With rFind
                sInp = .Text
                If IsBalanced(sInp, sOpn, sCls) Then
                    iWr = 0
                    sOut = Replace(Replace(sInp, sOpn, ""), sCls, "")
                    ReDim ab(1 To Len(sOut))

                    For iRd = 1 To Len(sInp)
                        Select Case Mid(sInp, iRd, 1)
                            Case sOpn
                                b = True
                            Case sCls
                                b = False
                            Case Else
                                iWr = iWr + 1
                                ab(iWr) = b
                        End Select
                    Next iRd

                    .Value = sOut

                    For iWr = 1 To Len(sOut)
                        If ab(iWr) Then .Characters(iWr, 1).Font.Subscript = True
                    Next iWr

                Else
                    .Interior.Color = vbYellow
                    sAddr = .Address
                End If
            End With

            Set rFind = Cells.FindNext(rFind)
            If rFind Is Nothing Then Exit Do
        Loop While rFind.Address <> sAddr
    End If
End Sub

Function IsBalanced(sInp As String, sOpn As String, sCls As String) As Boolean
    ' returns True if sInp has balanced opening and closing braces

    Dim i           As Long
    Dim n           As Long

    For i = 1 To Len(sInp)
        Select Case Mid(sInp, i, 1)
            Case sOpn
                n = n + 1
            Case sCls
                n = n - 1
                If n < 0 Then Exit Function
        End Select
    Next i

    IsBalanced = n = 0
End Function
 
Upvote 0
Revised for generality ...
Put the following into any two cells (doesn't matter if there is other properly formed values or not) and your code goes into an infinite loop...

P/1\Sn/2\C/3\Pb/4
 
Upvote 0

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,958
Latest member
Hat4Life

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top