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"

 

[Micron]

perhaps for the first part (although I suppose you'd want to change hard coded H to A1 cell reference):

Function replaceWithH() As String
Dim str As String
Dim i As Integer
Dim ary(3)
ary(0) = 3
ary(1) = 5
ary(2) = 15

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

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

End Function

Don't know if you'd need a return value (hence I posted a function) or not. If not you can change to a sub if desired.

 

[2Took]

How do you get the resulting formula from A2 to a clipboard?

 

[Micron]

I have worked with clipboard in Access but don't recall and would have to research that so will leave it up to you. Based on your first post you wanted to edit a sheet formula so why not do it directly instead of getting it onto the clipboard?

 

[2Took]

I have worked with clipboard in Access but don't recall and would have to research that so will leave it up to you. Based on your first post you wanted to edit a sheet formula so why not do it directly instead of getting it onto the clipboard?

I'd like it on the clipboard, so that it would be just a matter of pasting it into a cell. This is to streamline customization of a worksheet, which will have multiple cells with different formulas edited in this way.

 

[2Took]

I changed your code to a Sub, edited places, and referenced Range instead of hardcoded "H", but am getting below error on line:

str = Replace(str, Mid(str, ary(i) + 2, 1), Range(A1).Value, , 1)

Sub ReplaceA2A1()
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) + 2, 1), Range(A1).Value, , 1)
Next

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

End Sub

 

[2Took]

@Micron,

in A2 I have below formula:

="F" & ". " & F10 & " - " & INDIRECT("F"&L1)

After running the code, desired result is for F, in three places in A2, to be replaced with value of A1, e.g. with H, as follows:

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

Positions of those F, counting spaces, are:
3
15
39

I was able to run your code above, but result it prints in Immediate window is:

="F"HH ". " & F1H & " - " & INDIRECT("F"&L1)

Not a desired result, plus result needs to be ideally in the clip board for pasting, or changing the value in A2

 

[Micron]

Don't know what to tell you. I had this as a cell formula (followed by the result of the code):

="Billed "&C6&" pcs, Received "& B6+C6&" pcs ("&ABS(B6)&" pcs "&IF(B6<C6,"short","extra") & ")"
="BiHlHd "&C6&" Hcs, Received "& B6+C6&" pcs ("&ABS(B6)&" pcs "&IF(B6<C6,"short","extra") & ")"

Looks to me like the 3rd, 5th and 15th characters were replaced with "H"
I realize I didn't use your posted values (sorry, I forgot that I modified it for testing purposes - good catch). I can take a look at your actual formula and positions if that's not working. The error is probably because of where you put the code (mine was in a sheet module). However, you're on your own with using the clipboard.

 

[Micron]

Had a time out here. Have also notice that you allowed for the fact that =" is 2 positions already, which I didn't notice. I took it as 3rd position after = so the array or the expression will have to be modified (expression: would be to drop the +2, I think).

 

[Micron]

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