I am trying to write a macro in Excel VBA with the following properties:

replace "123" with "hello"

original	after replacement
12345678	hello45678 (123 is on the left)
123986604860	hello986604860 (123 is on the left)
037073058	037073058 (no 123)
99912398598	99912398598 (no replacement since 123 is in the middle)
123	123 (no replacement since it's alone)

My problem is the results always come up as 0 no matter what I type and I don't know what is wrong with that. The formula works when I type the formula in a particular cell but does not work in VBA. Could you please help to debug?

These are my codes:

Sub fastReplace() ' ' fastReplace Macro ' ' Keyboard Shortcut: Ctrl+d 'PURPOSE: Apply the Formula To A Cell Range

Dim rng As Range Dim DefaultRange As Range Dim FormatRuleInput As String Dim oldStr As String Dim newStr As String Dim length As Integer

'Determine a default range based on user's Selection If TypeName(Selection) = "Range" Then Set DefaultRange = Selection Else Set DefaultRange = ActiveCell End If

'Get the old text to be replaced oldStr = Application.InputBox( _ Title:="Fast Replace - Old Text", _ Prompt:="Please enter the old text to be replaced.")

'Get the new text newStr = Application.InputBox( _ Title:="Fast Replace - New Text", _ Prompt:="Please enter your new text.")

'Get A Cell Address From The User to Get Number Format From Set rng = Application.InputBox( _ Title:="Fast Replace", _ Prompt:="Select a cell range to apply fast replace", _ Default:=DefaultRange.Address, _ Type:=8)

'Test to ensure User Did not cancel If rng Is Nothing Then Exit Sub length = CInt(Len(oldStr)) 'Apply formula to Cell Range rng.Formula = "=IF(AND(LEFT(RC,length)=oldStr,CInt(Len(RC))>length),REPLACE(RC,1,length,newStr),RC)"

End Sub