Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 35

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

  1. #11
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I haven't downloaded the j-walk thing, but I have a fair idea of how it works. It should only take about 15 minutes to perfect my version though, but I wont get those 15 minutes in work today. I promise to do it tonight.

  2. #12
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right, I've got a solution here. I said 15 minutes, it longer, but the solution ended up being so simple I'm almost embarassed to say it took 30 minutes.


    Private Const CHAR_SUP As String = "^"
    Private Const CHAR_SUB As String = "|"

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If IsNumeric(Target.Value) Then Exit Sub

    'Look for SUPERSCRIPT character
    If InStr(1, Target.Value, CHAR_SUP) > 1 Then
    SuperScript Target, InStr(1, Target.Value, CHAR_SUP)
    End If

    'Look for SUBSCRIPT character
    If InStr(1, Target.Value, CHAR_SUB) > 1 Then
    SubScript Target, InStr(1, Target.Value, CHAR_SUB)
    End If

    End Sub
    Private Sub SuperScript(ByVal Target As Range, ByVal iPosition As Integer)
    Target.Characters(Start:=iPosition + 1, Length:=1).Font.SuperScript = True
    Target.Characters(Start:=iPosition, Length:=1).Delete
    End Sub
    Private Sub SubScript(ByVal Target As Range, ByVal iPosition As Integer)
    Target.Characters(Start:=iPosition + 1, Length:=1).Font.SubScript = True
    Target.Characters(Start:=iPosition, Length:=1).Delete
    End Sub


    EDIT:: I removed the LeftString and RightString functions because we don't need them.

    [ This Message was edited by: Mark O'Brien on 2002-04-24 08:26 ]

  3. #13
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WOW! Excellent job. This is terrific! Definitely goes into my keeper file.

  4. #14
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again Jay.

    Question for you though. I don't have the energy to think this one out, but it's going to be nagging me, and you might know the answer.

    This doesn't use a loop, but it appears to go though each character in the text. Why does it fix strings like:

    (H+)2SO2-4

    ?


  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    SUPER-GURU MARK O' BRIEN!!
    All bow to thee!!

    Super! Fantastic! Incredible!

    I love it! I have no idea how you thought this up!

    I do have one question, was my original idea even possible?

    [ This Message was edited by: Cosmos75 on 2002-04-23 13:32 ]

  6. #16
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How the hell would I know? Do you think I read the postings of paeons? (Just a joke)

    I think your idea would work, but it may be a hassle. I also think that's it's similar, in theory to the j-walk add-in that Jay suggested. (I think the add-in would maybe be a slightly better interface, I've still not downloaded that yet though.)

    EDIT:: You can get rid of these bits of code, as we don't use them:


    Private Function LeftString(ByVal sText As String, ByVal sSeparator As String) As String
    LeftString = Left(sText, InStr(1, sText, sSeparator) - 1)
    End Function
    Private Function RightString(ByVal sText As String, ByVal sSeparator As String) As String
    RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))
    End Function

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-23 14:13 ]

  7. #17
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    paeˇon Pronunciation Key (pn, -n)
    n.
    In quantitative verse, a foot of one long syllable and three short syllables occurring in any order.

    --------------------------------------------------------------------------------
    [Latin paen, from Greek pain, from pain, pain, paean. See paean.]

    Mark O' Brien,

    Thanks, at least I know that my idea is possible (even I don't have enough VBA knowledge to write such code).

    The add-in works good, although you have to type in your text and then format it character by character. I prefer your code which allows you to sub- or super-script text on the fly!

    You should submit this to some excel tips book or maybe to Mr.Excel for an award or recognition.

    [ This Message was edited by: Cosmos75 on 2002-04-24 14:08 ]

  8. #18
    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 came across this topic while looking for something like the solution here. Unfortunately, I don't know what to do with Mark O'Brien's code. I know how to get to the Visual Basic window, but I don't where to go from here.

    Your help would be appreciated.

  9. #19

    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

    dragonslayer22

    Can you show me same sample data and the result for it?

  10. #20
    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'm not sure what you mean. I don't have any sample data or results because I don't know how to use the program. How I can use the code that was presented here? How do I input it into Excel so that I can use it?

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
  •