CODEWITHSUNDEEP
excelvbastring-formattingstring-concatenationsuperscript
Oumayma El Jahrani
Oumayma El Jahrani

Reputation: 1

Concatenation of 2 strings and superscript

My issue is that i have 3 cells A1 , B1 and C1 ; A1 contains a number, B1 a string and C1 contains the concatenation of B1 and A1 . Let's say A1 contains the value 1 and B1 the value "Test" ; I want C1 to Contain Test1 but with 1 as a superscript . Here's the code i've written but that isn't working : 

Sub exposantmiseenforme()

    Dim i As Integer
    Dim C As Range
    Dim l As Integer

    l = Len(Range("B1"))
    C = Range("C1")

    With C.Characters(Start:=l, Length:=l + 1).Font
        .Name = "Calibri"
        .FontStyle = "Normal"
        .Size = 11
        .Strikethrough = False
        .Superscript = True
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

End Sub

Thank's in advance for your help !

Upvotes: 0

Views: 922

Answers (3)

David García Bodego
David García Bodego

Reputation: 1090

Try this one

Sub exposantmiseenforme()

Dim l As Integer

l = Len(Range("B1"))
Worksheets("Sheet1").Range("C1").Characters(l + 1, 1).Font.Superscript = True

End Sub

Hope it helps. (I am supposing that that cell is already formulated)

Upvotes: 1

JvdV
JvdV

Reputation: 75870

You could try the following (assuming you have more rows than just cells A1 & B1):

Sample data:

enter image description here

Code:

Sub SuperScriptTxt()

Dim rng As Range, cl As Range, lr As Long
With ThisWorkbook.Sheets("Sheet1") 'Change accordingly
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("C2:C" & lr)
    For Each cl In rng
        cl.Value = cl.Offset(, -2) & cl.Offset(, -1) 'Leave out this line, if concatenated values are actually already in place.
        cl.Characters(Len(cl.Offset(, -2)) + 1, Len(cl.Offset(, -1))).Font.SuperScript = True
    Next cl
End With

End Sub

Result:

enter image description here

Upvotes: 2

riskypenguin
riskypenguin

Reputation: 2199

You are missing a Set line 6:

Set C = Range("C1")

This is actually what you want to suberscript the last character of a string in C1 on the active sheet:

ActiveSheet.Range("C1").Characters(Start:=Len(Range("C1").Value), _
Length:=1).Font.Superscript = True

The way you wrote it it should superscript the entire text, not just the 1.

Upvotes: 1

Related Questions