VBA For Changing 2 to a Superscript when it's in a defined string

Question

so I tried to create a code just to do some basic formatting in the headers of long data sets. I have a fixsymbol code to change the um to micrometers with the proper symbol but when I try to run the code below to change the (um2) to a superscript 2 it flashes Error 13 type mismatch and the debug highlights this line "Position = InStr(c.Value, "µm2")" It still runs the code but spits the error at the end and if I try to run it on a raw data set not in a table it crashes Excel. How would I fix this error so I can have it run as a part of a larger script without crashing?

Sub ChangeToSuperScript()
Dim X As Long
Dim Position As Long
Dim c As Range
For Each c In Range("A:Z") 'range of cells
Position = InStr(c.Value, "µm2") 'change the number 2 into a potentiation sign
If Position Then c.Characters(Position + 2, 1).Font.Superscript = True
Next
End Sub

Thanks!

You're iterating over every single cell in columns A - Z... that's about 27.3 million cells. Maybe [find the last used cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first. — BigBen, Jul 19 '21 at 17:50
Try to replace `c.Value` with `c.Text` in `InStr(c.Value, "µm2")` — Алексей Р, Jul 19 '21 at 17:58

Tim Williams · Accepted Answer · 2021-07-19T23:12:23.700

2

You can screen out error values.

EDIT: updated to use SpecialCells to only operate on fixed values...

Sub ChangeToSuperScript()

    Dim ws As Worksheet, rng As Range
    Dim Position As Long, c As Range
    
    Set ws = ActiveSheet
    On Error Resume Next 'ignore error if no constants
    Set rng = ws.Cells.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0      'stop ignoring errors
    If rng Is Nothing Then Exit Sub 'no fixed values
    
    On Error GoTo haveError
    Application.Calculation = xlCalculationManual
    For Each c In rng.Cells
        Position = InStr(c.Value, "µm2")
        Debug.Print c.Address, c.Value, Position
        If Position > 0 Then
            c.Characters(Position + 2, 1).Font.Superscript = True
        End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
    
haveError:
    Debug.Print "Error:" & Err.Description
    Application.Calculation = xlCalculationAutomatic

End Sub

edited Jul 19 '21 at 23:12

answered Jul 19 '21 at 18:20

Tim Williams

154,628
8
97
125

Hi, so for this code I am using it on a wide variety of data sizes and each sheet is named depending on a treatment group so it is also dynamic. What would you suggest for the range to keep it dynamic in cases of data that's 5 columns long to 100 columns long? – DigitalPath3 Jul 19 '21 at 18:46
See edit above - `SpecialCells` does a lot of the checking so makes the code simpler – Tim Williams Jul 19 '21 at 19:28
Hi again, using the above I still get the same error of a Type Mismatch at the ''' If InStr(c.Value, "µm2") > 0 Then''' It runs but it puts the regular letter u as a subscript and doesn't touch the two. – DigitalPath3 Jul 19 '21 at 21:48
Sorry - I made a mess of that by editing it after copying it over. Should be OK now – Tim Williams Jul 19 '21 at 21:58
I am so sorry to bother again but excel keeps spitting the type mismatch on the line If InStr(c.Value,"µm2") > 0 Then' and it says error code 2042. The issue lies with the C.Value and I'm not sure how to fix that. – DigitalPath3 Jul 19 '21 at 22:04
What is the value of `c` when it fails? What's in that cell ? – Tim Williams Jul 19 '21 at 22:07
I can't see, it crashes part of the time but it does change the cells needed but where could I find the value where it fails? – DigitalPath3 Jul 19 '21 at 22:16
If Excel is actually crashing then something more serious is going on. If it's not crashing completely then try running the code above and review the Immediate pane in the VB editor afterwards. – Tim Williams Jul 19 '21 at 22:24
It produces no value for c, says 0 for position, and the immediate pane ends at C 31 which has the word mode with the cell to its right containing #N/A. It's ending in the first data statistics using the Excel Add-In right before the #N/A – DigitalPath3 Jul 19 '21 at 22:39
OK we can try suspending calculation while we make the changes - see edit. – Tim Williams Jul 19 '21 at 22:43
Stops running before the debug print, provides the same error at the same line with the type mismatch at the position=InStr line – DigitalPath3 Jul 19 '21 at 22:52
Looks like i forgot to post the updated code. If this doesn't fix it then you'll need to share a workbook which replicates the issue - upload to google drive/onedrive/box/etc and share a link. – Tim Williams Jul 19 '21 at 23:13

VBA For Changing 2 to a Superscript when it's in a defined string

1 Answers1