mcomp72
Active Member
- Joined
- Aug 14, 2016
- Messages
- 275
- Office Version
- 365
- 2019
- 2016
- 2011
- Platform
- Windows
- MacOS
I am trying to use the Replace function within VBA to remove a specific character from a string, at a certain position within the string. I don't think I am understanding how Replace works, but I've read a couple of different websites that talk about it, and it seems like I'm doing it right.
What I'm trying to do is remove a "$" from a formula in certain instances. The "$" will never be in the same position every time, so I have code that figures out where it is. But when I try to remove it from the string, instead of it removing only that one character, it removes it and everything to the left of it.
I condensed my code down so you could see what I'm trying to do.
I want the result of MyFormula to be: "=N36+'Page 1A'!$AM$61"
But when I run this sub, I get this: "6+'Page 1A'!AM$61"
I can't figure out what I'm doing wrong.
Is there a better way to remove one character from a string if you know the position of that character within the string?
What I'm trying to do is remove a "$" from a formula in certain instances. The "$" will never be in the same position every time, so I have code that figures out where it is. But when I try to remove it from the string, instead of it removing only that one character, it removes it and everything to the left of it.
I condensed my code down so you could see what I'm trying to do.
VBA Code:
Sub ReplaceACharacter()
Dim MyFormula As String
Dim FirstDollarSignPosition As Long
Dim SecondDollarSignPosition As Long
MyFormula = "=$N$36+'Page 1A'!$AM$61"
FirstDollarSignPosition = 2
SecondDollarSignPosition = 4
MyFormula = Replace(MyFormula, "$", "", SecondDollarSignPosition, 1)
MyFormula = Replace(MyFormula, "$", "", FirstDollarSignPosition, 1)
MsgBox MyFormula
End Sub
I want the result of MyFormula to be: "=N36+'Page 1A'!$AM$61"
But when I run this sub, I get this: "6+'Page 1A'!AM$61"
I can't figure out what I'm doing wrong.
Is there a better way to remove one character from a string if you know the position of that character within the string?