Excel VBA - replace multiple characters in a string by their position number

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
In A1 I have a letter character, e.g. "H"
In A2 I have a formula

Need VBA to replace multiple characters in A2 string, by their position number, with A1 value

Positions, counting spaces, are:
3
15
39

And another version, to replace by placeholder characters, i.e. if characters in A2 needing replacement with value in A1 were "XXXX"
 
with this
VBA Code:
Function replaceWithH() As String
Dim str As String
Dim i As Integer
Dim ary(3)
ary(0) = 3
ary(1) = 15
ary(2) = 39

str = Range("A2").Formula
For i = 0 To 2
   str = Replace(str, Mid(str, ary(i), 1), "H", , 1)
Next

'Debug.Print str
Range("A2").Formula = str

End Function
this
="F" & ". " & F10 & " - " & INDIRECT("F"&L1) becomes this
="H" & ". " & H10 & " - " & INDIRECT("H"&L1)

="F" & "." could be a bit simpler? ="F. " & F10
Thanks, @Micron

Couple of questions, though:

1. Where am I supposed to put that Function? (I am more familiar with a Sub use, and I run your initial code as a Sub from a Module)
2. How do I then use it?
3. I need the A1 as a source of what to replace with, not "H" hardcoded
4. Where does the result of your Function goes? I need it to change the A2 formula with the replacements, then I can just have VBA select A2 and Copy
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I don't know where you need to put it. It depends on how/from where you want to run the code - sheet button, macro dialog from the ribbon, userform... You need to reveal that in your threads about code.

The differences between sub and function is that a function can return a value, a sub cannot. So if you call the function by putting =replaceWithH(M5) and edit M5, the function would return a value to cell M5. Or you might use sheet change event, which would fire no matter which cell you edit (probably not in this situation). In those cases code would have to be in a sheet module. There you can use Range(C5) type of reference (referring to your posted error from earlier here). In a standard module you cannot - have to preface that with a sheet reference at least as the code now has no idea what sheet Range(C5) is referring to. You might want to research vba scope or similar for more info. So re H vs A1:

str = Replace(str, Mid(str, ary(i), 1), "H", , 1) would become
str = Replace(str, Mid(str, ary(i), 1), Range("A2") , 1) or whatever A2 should be. I'm assuming you put the code in a sheet module, otherwise str = Range("A2").Formula would not work as I've noted.
 
Upvote 0
Please just answer my questions as narrow as possible, focus:

1. Where do I need to put your Function code in order to then use =replaceWithH(M5) on the worksheet?
2. What is the role of M5? Why M5? I am trying to get my formula, that I have in A2, to be affected with Value in A1 by replacing some characters in A2 with character from A1.
 
Upvote 0
You're the one who needs to focus. I've told you twice - it depends on how you're calling the code. I mentioned at least 3 different possible ways, none of which you confirmed. I'm going to unwatch this one now. Good luck.
 
Upvote 0
You're the one who needs to focus. I've told you twice - it depends on how you're calling the code. I mentioned at least 3 different possible ways, none of which you confirmed. I'm going to unwatch this one now. Good luck.
@Micron,

I was not intending to offend you. My apologies if that's how it came across. If you can find it in yourself to address my questions in #13 post above, I'd be most appreciative.
 
Upvote 0
In A1 I have a letter character, e.g. "H"
In A2 I have a formula

Need VBA to replace multiple characters in A2 string, by their position number, with A1 value

Positions, counting spaces, are:
3
15
39

And another version, to replace by placeholder characters, i.e. if characters in A2 needing replacement with value in A1 were "XXXX"

UPDATE:

Maybe this description will help better to understand the objective:

A2 has a formula:

="H" & ". " & H10 & " - " & INDIRECT("H"&L1)

A1 has a character: K

Desired outcome in A2 for all three H to be replaced with value of A1:

="K" & ". " & K10 & " - " & INDIRECT("K"&L1)

Then for A2 to be copied to clipboard by VBA
As soon as pasted, for A2 to return back to initial formula:

="H" & ". " & H10 & " - " & INDIRECT("H"&L1)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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