2

I'm adding footnotes (always adding footnotes) to a table and I would really like to be able to automate the repetitive nature of making my symbols superscript.

Currently it’s: >enter cell>highlight last character> click on Font box (2007 version) to open up the options to use superscript>tick superscript box>hit okay> and move on to next.

Any of you have ideas how I can say: >look in column A down to Row 400 where>you find “Symbol” make last charter of that string (the sysmbol in fact) superscript>go to next one.

I’ve tried recording some of my actions but it reads superscript FALSE. So I must be doing something wrong.

Any ideas or examples on how to do this?

Improve this question
3
  • Can you post the vba code you have already built / generated ? And tell us precisely where it is going wrong (superscript False or else) ? That would help us to help you.
    – JMax
    Commented Jun 22, 2011 at 13:09
  • I deleted the code as it was generated from the macro recorder. Therefore very cell specific. I could create it again, and add it to the post if you would like?
    – RocketGoal
    Commented Jun 22, 2011 at 14:12
  • Seems like Lance Roberts answered with his own code. We'd better start the discussion from his vba
    – JMax
    Commented Jun 22, 2011 at 14:16

1 Answer 1

Reset to default
1

Create a Macro that will trigger off of the WorksheetChange event, then do your test for your symbol, then change it to Superscript if it's there. This will trigger every time you change a cell:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Then
    Exit Sub
  Else
    If Right(Target.Value, 1) = CHR(128) Then
      Target.Characters(Start:=Len(Target.Value), _
                        Length:=1).Font.Superscript = True
    End If
  End If
End Sub
Improve this answer
9
  • Hi. If I change <symbol> to <€> then it highlights the row in red and gives me the message "expected expression". Any ideas?
    – RocketGoal
    Commented Jun 22, 2011 at 14:11
  • @Mike, you can't use the < symbols of course, that was just put there so you would know that you need to sub the string you want in. You can try quotes, though if you always know your symbol, I'd probably use the CHR() value that works for it (though that non-ascii symbol might not have one).
    – Lance Roberts
    Commented Jun 22, 2011 at 14:12
  • Thanks. I'll try quotes and then have a look for the CHAR character. Much apppreciated.
    – RocketGoal
    Commented Jun 22, 2011 at 14:18
  • Seems to bug for the moments on merged cells. Is there anyway I can make this function look at a particular range (A5:A500)?
    – RocketGoal
    Commented Jun 22, 2011 at 14:23
  • My aim is to build a few nested functions that look for specific symbols and can speed up this whole mind numbing exercise. But I'm trying to see how something like this looks first (understand if possible). The macro recorder was very cell specific and long just for one cell, and that was without looping through a whole range.
    – RocketGoal
    Commented Jun 22, 2011 at 14:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .