Query in excel to combine cells with Superscript and subscript by UDF or Macro 4

[raviprakash369]

Hi all,
Again came with some query as I tried a lot and not able to get the result.
Question is there are 3 cells in excel with inputs like 20 in 1st cell, +0.2 in 2nd cell, -0.1 in third cell. we need a Macro or UDF to get output in a single cell with 20 and +0.2 as superscript, -0.1 as subscript.
I have tried the below formula which can combine the 3 cells and not resulting with superscript and subscript.
=TEXT(CONCATENATE(O24,IF(P24>0,"+"&P24,"-"&P24),IF(Q24>0,""&Q24,"-"&Q24)),"@") where P24,Q24 and O24 are display cell numbers.
If anyone knows please help.

 

[SkipVought]

Hi,

You'll have to download my workbook solution to make sense of these instructions.
[tt]
The value is represented in a camera Tool feature.
I have formatted the Camera Tool Picture with a solid line border in RED. You can remove that formatting.
It is linked to O24. The limits are text values linked to P24 & Q24.
You can ADD the Camera Tool to your Quick Access Toolbar(QAT) by
1. Right-click in QAT. Select Customize Quick Access Toolbar...
2. Choose commands from : All Commands
3. Scroll to Camera and Add >> to your QAT
4. OK
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Hi SkipVought,
I have gone through the file. Thanks for your effort and time.
Sorry,the camera option won't work for the requirement as we need output in single cell with +0.2 as superscript and -0.1 as subscript next to 20.

Thanks a lot for the help anyway.

 

[SkipVought]

The best you'll get in one cell is like...
[tt]
20[sup]+0.02[/sup][sub]-0.01[/sub]
[/tt]

I don't believe there's a way to OVERSTRIKE in Excel, which is the technique to use if it were possible.

You would have to interleave, it would appear, the characters of the limits, like
[tt]
20[sup]+[/sup][sub]-[/sub][sup]0[/sup][sub]0[/sub][sup].[/sup][sub].[/sub][sup]0[/sup][sub]0[/sub][sup]2[/sup][sub]1[/sub]
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Yes, the number you have written is the exact output looking for.

 

[SkipVought]

Do you know how to do that manually?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Sub ValueTolerance()
'SkipVought 2017 3.14
'target values in O, P,Q in row 24[b]
'SELECT the cell to contain the result[/b]
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Integer, p2 As Integer
    
    sVal = [O24]
    
    sMax = "+" & [P24]
    
    sMin = "-" & -[Q24]
    
    With ActiveCell
        .Value = sVal & sMax & sMin
    
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

This slight modification places the result in any column on the row where the values in columns O, P & Q are the value, max and min tolerance.

Sub ValueTolerance()
'SkipVought 2017 3.14
'target values in O, P,Q in row of the ActiveCell[b]
'SELECT the cell to contain the result[/b]
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Integer, p2 As Integer
    
    With ActiveCell
        sVal = Cells(.Row, "O").Value           'The value
        
        sMax = "+" & Cells(.Row, "P").Value     'The max tolerance
        
        sMin = Cells(.Row, "Q").Value           'The min tolerance
    
        .Value = sVal & sMax & sMin
    
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

It works fine. Thank you very much for your time and effort. Really your help is highly appreciated.
Now,I got some idea and will try to do with any row and any column which can select the particular 3 cells required and give the output with superscript and subscript.

Thanks a ton.

Regards,
Ravi

 

[IDS]

Skip - that's a very useful little macro, as a demo of formatting strings from VBA as well as for the specific application with sup and subscripts.

I have taken the liberty of amending the code to use the selected range as the input values (with any number of rows). If there are 4 or fewer columns selected the results go in the fourth column, or if more they go in the last column.

Is it OK if I post the code on my blog?

Code edited; added "i as Long" to Dim statement

Sub ValueTolerance2()
'SkipVought 2017 3.14
'Ammended to use selected range as input: Doug Jenkins 2017 3.15

'Output to the column to the right of the input range, or to the last column of selected range if NumCols > 4
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Long, p2 As Long, NumRows As Long, NumCols As Long, i as Long
    Dim RowVals As Range

    With Selection
        NumRows = .Rows.Count
        NumCols = .Columns.Count
        If NumCols < 4 Then NumCols = 4
        For i = 1 To NumRows
            Set RowVals = .Cells.Offset(i - 1, 0).Resize(1, NumCols)
            
            sVal = RowVals(1, 1).Value           'The value
            
            sMax = "+" & RowVals(1, 2).Value     'The max tolerance
            
            sMin = RowVals(1, 3).Value           'The min tolerance
        
            With RowVals(1, NumCols)
                .Value = sVal & sMax & sMin
        
                p1 = InStr(.Value, "+")
                p2 = InStr(.Value, "-")
            
                With .Characters(Start:=p1, Length:=p2 - p1).Font
                    .Superscript = True
                    .Subscript = False
                End With
                
                With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
                    .Superscript = False
                    .Subscript = True
                End With
            End With
        Next i
    End With
End Sub

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Sure. Thanks for asking.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

@ Doug Jenkins, I have tried the code posted and getting error "cannot execute code in break mode"

 

[SkipVought]

Ravi, works for me. I just needed to add a declaration for i.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

I just needed to add a declaration for i.

Oops.

Some background on declaring variables for Ravi:
By default you don't have to declare variables, so if you forget to add "Dim i as long" when you add a new variable i, the code will still work. This is not a good idea though for several reasons, including:
- If you mis-type a variable name the program will treat it as a new variable, rather than giving you an error message
- If anyone copies and pastes your code, and they have settings that require all variables to be declared, the code won't work on their computer.

To require variables to be declared in any code in a module add:
Option Explicit
as the first line in the module.

To get this line automatically added to all new modules when you create them, go to Tools-Options and select "Require Variable Declaration" That will then become the default in all new files.

I had forgotten to change that setting on my new computer.

Your error message "cannot execute code in break mode" indicates that the code is already running, so you can't restart it. Try opening the VB Editor and pressing the F8 key, which will either step through your code, or give you a different error message, indicating what the real problem is.

If you still can't get it to work, upload your spreadsheet and I will have a look.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Ravi, you know that all you have to do is ...

1) select the O,P,Q cells in all the rows that contain data and
2) run the macro

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Thank you both. Really I am learning new things from you. As a design engineer it is a big challenge for me to get results like this in excel. You guys inspired me to learn a lot.
Thanks for your unconditional love

 

[raviprakash369]

One more thing I found is : If value in any input cell is edited after running the macro, the result will be same as before. Associativity is not there after editing the input cells.

 

[IDS]

Associativity is not there after editing the input cells.

The problem is that if you want to return a formatted string, then the only way to do it is with a macro, which you will need to re-run every time anything changes.

If you want an active formula (either using standard functions, or VBA user defined functions) you will have to settle for a string that doesn't need special formatting. Something like:
123.45 +0.00/-0.02
could be done, but not if you want subscripts or super scripts.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

You can "automatically" run this macro using the WorkSheet_Change Event.

Plz upload your workbook.




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[raviprakash369]

Hi,

Got the code for superscript and subscript and thought of sharing here.
This macro assumes that you will have 3 cells selected prior to calling it.
Once run, it will ask you to select Target cell for output.

Public Sub ValueTolerance2()
Dim rngSel As Range, rngTgt As Range
Set rngSel = Selection
'\\ Check if user has selected 3 columns and 1 row
If Not (rngSel.Columns.Count = 3 And rngSel.Rows.Count = 1) Then
    MsgBox "Selection must be 3 columns by 1 row only!", vbInformation
    Exit Sub
Else
    If Application.CountA(rngSel) < 3 Then
        MsgBox "One or more cells are empty!", vbInformation
        Exit Sub
    End If
End If
'\\ Select Range
On Error Resume Next
    Set rngTgt = Application.InputBox(Prompt:="Select Target Cell!", Type:=8)
    Err.Clear
On Error GoTo 0
'\\ Check if user has made valid selection or not.
If Not rngTgt Is Nothing Then
    With rngTgt
        .Value = rngSel.Cells(1, 1).Value & IIf(InStr(rngSel.Cells(1, 2).Value, "+") > 0, rngSel.Cells(1, 2).Value, "+" & rngSel.Cells(1, 2).Value) & _
        IIf(InStr(rngSel.Cells(1, 3).Value, "-") > 0, rngSel.Cells(1, 3).Value, "-" & rngSel.Cells(1, 3).Value)
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
Else
    MsgBox "No cell selected for output!", vbExclamation
End If
End Sub