Maintaining Superscript and Subscript format when using Replace

[theflyingdutchdog]

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

 

[pgc01]

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

 

[theflyingdutchdog]

I'm not understanding what you mean. How would you do that? How do I change everything else and not change the subscripts?

 

[pgc01]

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^#d_#efg

What I want in Sheet2!A1 is: abc^\#d_\#efg

The replacements respect the formats.

Does this help?

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

 

[theflyingdutchdog]

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.

 

[theflyingdutchdog]

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?

 

[theflyingdutchdog]

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.

 

[theflyingdutchdog]

What exactly did you do to get it to work on your sample? Would this work if the symbol didn't have any #'s or _'s to replace?

 

[theflyingdutchdog]

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!

 

[pgc01]

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

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