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

[raviprakash]

I am design engineer and new to excel. Need some help in excel. My 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. If anyone knows please help.

 

[vletm]

An one version of Your idea...

 

[raviprakash]

Below is the code used to get result but restricted to particular O, P & Q Cells.

Sub ValueTolerance()
'target values in O, P,Q in row of the ActiveCell
'SELECT the cell to contain the result
  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

[CODE]

Need a macro which can select the 3 cells where ever selected and give output in single cell.

If anyone knows please help.

Thanks in advance.

Regards,
Ravi

 

[vletm]

@raviprakash, design engineer and new to excel
You 'Asked An Excel Question' and
now You show ... 'VBA Macros'-case.
Some people like this ... some people ... not so much.
You wrote interesting sentence:
Need a macro which can select the 3 cells where ever selected and give output in single cell.
Would/could You show visual view of You 'Question'?

 

[raviprakash]

@vletm , below is the ouput needed.

 

[vletm]

Okay no comments of that sentence...
This makes output to selected single cell if You follow instructions.

 

[raviprakash]

@vletm , Thanks for your support and time. I checked the excel and sorry that it won't work out. We need output in a single cell with superscript and subscript. If you see the above code, it works only for particular cells and need modification to that to select any 3 cells and get the result.

 

[vletm]

@raviprakash ... hmm
It makes output in a single cell with superscript and subscript and You want!
Your 'to select any 3 cells'
... would mean like "A1", "Z2300" & "AS34567" ... where the result?
and which of those would be 'value', 'superscript' and 'subscript'?
Next time cells could be "C3000", "ZZ1" & other sheets "CV345"
... how to handle?

 

[shrivallabha]

See if below code will help your case. It 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

 

[raviprakash]

@shrivallabha , Thanks a ton for your time and effort.
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.

 

[shrivallabha]

@shrivallabha , Thanks a ton for your time and effort.
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.

So it works as you need?

Please note that this is not an event based code which will update target cell dynamically. Event based one will be tricky to work on. So you will have to run it manually.

 

[raviprakash]

Yes, it works fine. Thanks a lot.