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

 

[theflyingdutchdog]

It works except for one thing. If I had a # or _ or anything else in front of a subscript, it does nothing to it at all. It keeps the subscript the same, but it doesn't even copy over the original #.

So if I had V#(subscript)DDQ on Sheet1, I would only have V(subscript)DDQ on Sheet2. Same for the underscore.

Btw, you are a genius. Thank you!

 

[theflyingdutchdog]

I guess this code is assuming that I only have one character preceding a subscript?

 

[theflyingdutchdog]

Nevermind. I'm being a dumbass. I just have to copy whatever is on the first worksheet to another worksheet to get the changes.

 

[theflyingdutchdog]

Except that it copies the cells with the subscripts over, but it doesn't do any of the \_ or \# on them. So if I had V_n(subscript)SSQ, I would just get V_n(subscript)SSQ for the other sheet.

****...what do I do about those?

 

[theflyingdutchdog]

Also, would you mind explaining how your program works? I really need to learn this. How does the For statement work to walk through the rows? What is the 1 Step -1?

What is Select Case Mid()? And what is the j value you passed in as an argument? Why do you use .Delete? What are you deleting.

Thank you so, so much!

 

[pgc01]

Also, would you mind explaining how your program works? I really need to learn this. How does the For statement work to walk through the rows? What is the 1 Step -1?

What is Select Case Mid()? And what is the j value you passed in as an argument? Why do you use .Delete? What are you deleting.

Thank you so, so much!

Sorry, no.

I don't have much free time.
I thought you knew vba and that you were looking for some pointers.

As it happens you don't know programming and so you cannot write any solution to solve your problem, you want someone to solve the problem for you.

If you want to learn how to program in vba you can google it and you'll find lots of sites that you can explore.

For the present problem I hope someone else wants to do it for you, else you'll have to hire someone.
Since this is a very simply problem any college kid near you will welcome some extra money to work with you on this.

 

[theflyingdutchdog]

wow, that was incredibly harsh! No, you are right, I don't know any vba, but I do know other programming languages. And no, I didn't want someone to just do it for me, which is why I asked about the specific lines of code and what they mean. Fine, I can look up the functions just fine, but I didn't know why say, I couldn't write rng = ActiveSheet("Sheet1").Select etc as opposed to assigning it such as rDest = Worksheets("Sheet1").Cells etc.

I am in college. I am a CS major. I don't know vba nor do any of my classmates nor any of my coworkers. None. Not one. They say they avoid it at all cost as I wish I had. It's surprisingly hard to learn this esp in the short time frame I was given. Vba isn't flexible. Nothing online addressed the specific problems I was having.

So if I had a subscript, I could find it in Vba, but I didn't know how to change part of a string where only part is a subscript.

There was no reason to be insulting and hurtful esp when I was only looking for explanation of the code. If you didn't have time, you could have left it at that.

But thanks. The code did help. No, I don't know vba. And now I wonder if I didn't do the wrong thing majoring in this. If I did, it's too late to change now. < CUT >, and now maybe I can't do this. This is my first assignment at my first programming job, and I can't do it. I'm trying not to cry at my cubicle, but I don't know what to do from here if I can't do this.

I got it to work by also using Word, but you're right. I should be able to do this. I don't know why I don't exactly understand what that line of code is doing. I know roughly what it's doing. What do I do now?

 

[pgc01]

Well, sorry it it sounded harsh.

My native language is not English and so maybe when I want to be clear it sounds harsh.

If you knew vba then you'd be looking for advice on which objects and properties/methods to use. This is what I understood you were looking for when I read your first post.

Now I saw that not only you do not know vba but also don't know visual basic, which is the base for vba and the classical beginners programming language. This will make it difficult for you to understand the code both in terms of the syntax and in terms of the behaviour of the objects. This also means that even if you have some base code you'll find it difficult to adapt it to your real problem.

This is why I think that you'd be better off working with someone that knows the language and the excel object model. You know what you want, you'd get help on the implementation, you'd know that the code is really doing what you nead and it would be easier for you to tweak it when necessary.