Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 35

Thread: Superscript Subscript - part of a cell - using VBA - Part

  1. #21

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    If you don't have any data to work with, why do you need to use it ?

  2. #22
    New Member
    Join Date
    Apr 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    I just haven't applied it to anything yet. I need for chemical formulas and equations with subscripts and superscripts on variables. How about these: C2H4 and Ka = PSa/P (where is a superscript and the a's are subscripts).

  3. #23

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    1) Hit Alt + F11
    2) go to [Insert] - [Module] then paste the code onto the right pane
    3) hit Alt + F11 again
    4) Hit Alt + F8
    5) choose "Chemical_Notation" then click on Run
    Code:
    Sub Chemical_Notation()
    Dim myStr As String, m As Object
    myStr = "C2H4 and Ka = PSa/P"
    Range("a1").Value = myStr
    With CreateObject("VBScript.RegExp")
        .Pattern = "[A-Z](\d|a)"
        .Global = True
        If .test(myStr) Then
            For Each m In .execute(myStr)
                Range("a1").Characters(m.firstindex + 2, 1).Font.subscript = True
            Next
        End If
    End With
    End Sub

  4. #24
    New Member
    Join Date
    Apr 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    I followed your instructions. When I ran the macro at the last step, Excel did not output anything. What am I doing wrong? I'd like to be able to use | and ^ to denote subscripts and superscripts.

  5. #25

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    Quote Originally Posted by dragonslayer22 View Post
    I just haven't applied it to anything yet. I need for chemical formulas and equations with subscripts and superscripts on variables. How about these: C2H4 and Ka = PSa/P (where is a superscript and the a's are subscripts).
    Didn't you see A1 entered as "C2H4 and Ka = PSa/P" ?

  6. #26
    New Member
    Join Date
    Apr 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    Sorry, I see it now. I tried it in a sheet that had other things in it and didn't notice when it appeared in A1. How can I change it so that it works with the | and ^ marks to indicate the changes?

  7. #27

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    Well, I haven't read this thread throughly.

    What do you want to do with ^ and | thing ?

  8. #28
    New Member
    Join Date
    Apr 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    I want to be able to type C|2H|4 and have the macro convert the character following | to subscripts. For, P^S that should convert the 'S' to a superscript.

  9. #29
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,950
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    dragonslayer

    I recommend you start a new thread - the original is 7 years old, and I'm not even sure it is relevant to what you want to do.

    Where exactly is the data you want to work with coming from?

    If it's theresult of a formula there's no way to super/subscript without changing it to an actual value.

    If you are just entering the data manually then we can probably come up with event code to do the formatting.
    If posting code please use code tags.

  10. #30

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Superscript Subscript - part of a cell - using VBA - Part

    How about
    Code:
    Sub test()
    Dim r As Range, temp As String, sInd As String, ssInd As String
    Range("a1:b1").Value = Array("C|2H|4","P^S")
    With CreateObject("VBScript.RegExp")
        For Each r In Range("a1:b1")
            .Pattern = "\|"
            temp = r.Text
            Do While .test(temp)
                x = .execute(temp)(0).firstindex + 1
                sInd = Ind & "," & x
                temp = Application.Replace(temp, x,1, "")
            Loop
            .Pattern = "\^"
            Do While .test(temp) 
                x = .execute(temp)(0).firstindex + 1
                ssInd = Ind & "," & x
                temp = Application.Replace(temp, x,1, "")
            Loop
            r.Value = temp
            If Len(sInd) Then
                For Each e In Split(Mid(sInd, 2))
                    r.Characters(e, 1).Font.Subscript = True
                Next
            End If
            If Len(ssInd) Then
                For Each e In Split(Mid(sInd, 2))
                    r.Characters(e, 1).Font.SuperSubscript = True
                Next
            End If
        Next
    End With
    End Sub
    Last edited by Seiya; Apr 15th, 2009 at 04:54 AM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •