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

[2Took]

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"

 

[2Took]

with this

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

 

[Micron]

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.

 

[2Took]

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.

 

[Micron]

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.

 

[2Took]

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.

 

[2Took]

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)