Maintaining Superscript and Subscript format when using Replace

theflyingdutchdog

New Member
Joined
Jun 2, 2017
Messages
39
So I have to take symbols for electronic components (like CK and CK#), and I have to come up with a program that formats them in Latex.

I typed in the original symbols to be formatted in separate columns in Excel. Then I created a column next to each column of the original symbols. What I am being told to do is come up with a program (VBA or otherwise) that will automatically format the original column of symbols to Latex and put those new Latex symbols in a column adjacent to the original.

So if I have CK# in Column A, I need a program that will spit out CK\# in Column B.

My two main problems are superscripts/subscripts and overline characters. To put superscipt/subscript characters in, I thought I could use Word's Find and Replace after running my code. (I still haven't figured out overline.) But my code with the Replace function changes the format of the subscripts/superscripts to regular font.

How do I keep my code with the Replace function from changing the format of subscripts/superscripts?

Here's my code:

Sub Latexualize()


Dim rng As Range
Dim cell As Range
Dim txt As String
Dim wsOut As Worksheet


Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
Set wsOut = ActiveWorkbook.Sheets.Add(Type:=xlWorksheet)


For Each cell In rng
txt = cell.Value


' Add Replace() steps below, as needed
txt = Replace(txt, "", "\backslash")
txt = Replace(txt, "_", "\_")
txt = Replace(txt, "#", "\#")
txt = Replace(txt, ChrW(916), "\Delta")
txt = Replace(txt, ChrW(&H221E), "\infty")


' Output to new worksheet
wsOut.Cells(cell.Row, cell.Column).Value = txt
Next


End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi

Excel vba does not have rich text string variables. You cannot use string variables and keep the formats.

If you want to do the replacements in excel and keep the formats you can

1 - copy the source cell to the destination cell, this keeps the formats
2 - edit the destination cell and do the replacements using the .Characters property of the Range object
 
Upvote 0
Hi

This is a small example.

You want to copy the value in cell A1 in Sheet1 to A1 in Sheet2.
Also replace "_" with "\_" and "#" with "\#" keeping the formats.

What I do is

1 - Copy Sheet1!A1 to Sheet2!A1. This copys the cell keeping the formats.

2 - loop through the characters in Sheet2!A1 and do the replacements.
To keep the formats I use the .Characters property of the Range object.


In this test I wrote in Sheet1!A1: abc<sup>#d</sup><sub>#e</sub>fg

What I want in Sheet2!A1 is: abc<sup>\#d</sup><sub>\#e</sub>fg

The replacements respect the formats.

Does this help?


Code:
Sub CopyKeepFormat()

Dim rSrc As Range, rDest As Range
Dim j As Long

    Set rSrc = Worksheets("Sheet1").Range("A1") ' source cell
    Set rDest = Worksheets("Sheet2").Range("A1") ' destination cell
    
    ' first copy source cell to destination cell, this keeps the formats
    
    rSrc.Copy Destination:=rDest
    
    ' replace "_" with "\_" and "#" with "\#" keeping the formats

    For j = Len(rDest.Value) To 1 Step -1

        Select Case Mid(rDest.Value, j, 1)
        
            Case "_":
                rDest.Characters(j + 1, 0).Insert ("\_")
                rDest.Characters(j, 1).Delete
                
            Case "#":
                rDest.Characters(j + 1, 0).Insert ("\#")
                rDest.Characters(j, 1).Delete
                
            Case Else:
            
        End Select

    Next j

End Sub
 
Upvote 0
But copying and pasting the columns to Sheet2 undoes the format. It takes out the superscripts and subscripts.

If this works, I am giving you my first born. Please, sweet baby Jesus.
 
Upvote 0
I got an error. The error says: "Run-time error '9': Subscript out of range. ohhhh I see. i didn't look at the cells. How do I correct the range to include the whole column and/or worksheet?
 
Upvote 0
Two things happened.

(1) When I run the code as you have it, after copying and pasting the one sheet to a separate sheet in the same workbook, the contents of Cell A1 are cut out in Sheet2. It eliminates the value of Cell A1 in the sheet which the code is running on. Of course, I checked to see that the subscripts and formatting stayed the same on the copied sheet.

(2) When I type: Set rSrc = Worksheets("SymbolCompareJESD79(BeforeLatex2").Range("A1:A64") I get an error which reads: "Run-time error '9': Subscript out of range.

Please help.
 
Upvote 0
Okay, so when I put in individual cell values, you are right that it keeps the subscript formatting! Which is amazing btw! Now I just have to figure out how to do this over a range, not just a cell.

Thank you!
 
Upvote 0
OK

This is the same example, but instead of a cell it loops through the cells in the range Sheet1!A1:B10 that have a constant text value.

Each of these cells are copied to the corresponding cell in Sheet2 (same row and same column) and then the replacement is done.

See if this helps.

To test write some constant string values in Sheet1!A1:B10, execute the code and check Sheet2!A1:B10


Code:
Sub CopyKeepFormat()


Dim r As Range, rSrc As Range, rDest As Range
Dim j As Long

    Set r = Worksheets("Sheet1").Range("A1:B10").SpecialCells(xlCellTypeConstants, xlTextValues)
    
    For Each rSrc In r
    
        Set rDest = Worksheets("Sheet2").Cells(rSrc.Row, rSrc.Column) ' destination cell
        
        ' first copy source cell to destination cell, this keeps the formats
        
        rSrc.Copy Destination:=rDest
        
        ' replace "_" with "\_" and "#" with "\#" keeping the formats
    
        For j = Len(rDest.Value) To 1 Step -1
    
            Select Case Mid(rDest.Value, j, 1)
            
                Case "_":
                    rDest.Characters(j + 1, 0).Insert ("\_")
                    rDest.Characters(j, 1).Delete
                    
                Case "#":
                    rDest.Characters(j + 1, 0).Insert ("\#")
                    rDest.Characters(j, 1).Delete
                    
                Case Else:
                
            End Select
    
        Next j
    
    Next rSrc

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top