0

I want to execute Replace method on Range object. After so much trials, laconic errors from debugger, and reading documentation, I decided to ask for help.

Why do my Replace methods not work? What about those commented ones?

Sub Convert_Sheet()
  Dim set_1 As Range
  Set set_1 = ActiveSheet.Range("A3:AZ300")

  'set_1.Replace(TextBoxSourceJobPrefix.Text, TextBoxTargetJobPrefix.Text, xlWhole, xlByRows, True, )
  'Set set_1 = Replace(set_1.Value, UCase(TextBoxSourceNode.Text), UCase(TextBoxTargetNode.Text))

  set_1.Replace What:=TextBoxSourceJobPrefix.Text, Replacement:=TextBoxTargetJobPrefix.Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
  set_1.Replace UCase(TextBoxSourceNode.Text), UCase(TextBoxTargetNode.Text)
  set_1.Replace TextBoxSourceGroup.Text & "G", TextBoxTargetGroup.Text & "G"
  set_1.Replace TextBoxSourceGroup.Text & "Z", TextBoxTargetGroup.Text & "Z"
  set_1.Replace LCase(TextBoxSourceNode.Text), LCase(TextBoxTargetNode.Text)
  set_1.Replace TextBoxSourceJobOwner.Text, TextBoxTargetJobOwner.Text
End Sub

Each time I get Object required error:

enter image description here

Even if I opaque with With:

  With set_1
    .Replace What:=TextBoxSourceJobPrefix.Text, Replacement:=TextBoxTargetJobPrefix.Text, MatchCase:=True        
    .Replace What:=TextBoxSourceJobPrefix.Text, Replacement:=TextBoxTargetJobPrefix.Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
  End With

Replace syntax (a method of Range object):

enter image description here

So there IS a function that doesn't need as first parameter the String to be replaced. But why do all my trials fail? Do I have to execute a command with all the parameters? I need only one - MatchCase.

Is there a way to replace characters in range in bulk mode? (not using Replace(text, toReplace, ReplaceWith) )

I already found those questions helpful, but I still get errors:

excel vba replace failure

Excel VBA Replace with Array Groups

Improve this question
12
  • What are the values of TextBoxSourceJobPrefix.Text and TextBoxTargetJobPrefix.Text when it crashes? Commented Feb 24, 2017 at 8:54
  • 4
    What if there is a typo in the names of those text-boxes? Did you set Option Explicit? Commented Feb 24, 2017 at 9:00
  • 1
    (a) No, the error would not be due to empty value of the replacement string (if that is what you mean by "Target"). (b) Having checked that TextBoxSourceJobPrefix.Text and TextBoxTargetJobPrefix.Text both return valid strings, check that Set_1 is still valid - in the Immediate window, type in ?Set_1.Address and ensure that it shows $A$3:$AZ$300. Commented Feb 24, 2017 at 9:25
  • 1
    Don't use Paranthesis since it required only when you are setting it in to some variable. In the above case no parenthesis are needed. Commented Feb 24, 2017 at 9:26
  • 1
    Thanks @A.S.H for Option Explicit. TextBoxSourceJobPrefix is not initialized. Checking on ways to make it visible or initialized. Commented Feb 24, 2017 at 10:30
Related questions
2
Find characters and replace
0
Find and replace multiple character in a string VBA
0
Replace multiple cells
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.