Find and Replace Macro - Object error

[squeakums]

I'm trying the code below as recorded to find and replace. It works when I record it, but then I go back to original unedited file and try to run this macro where the headers are back to original - I get an error message? What's the best find and replace. I just want to replace a few header names and they might not always be under the same column. The error message is object variance or block variance not set?

Rows("1:1").Select
Selection.Find(What:="OLH: Task 1 Due Date", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="OLH: Task 1 Due Date", Replacement:= _
"DDQ Order Due Date", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
xlReplaceFormula2
Selection.FindNext(After:=ActiveCell).Activate

 

[Joe4]

Was is the exact error message you are getting?

Note that with Find/Replace, it will return an error if it does not find what you are looking for.

You can avoid this by setting the cell it is located in equal to a range variable, and checking to see if that range is empty (i.e. "Nothing"), something like this:

Dim rng As Range

Set rng = Rows("1:1").Find(What:="OLH: Task 1 Due Date", After:=Range("A1"), LookIn:= _
    xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)

If rng Is Nothing Then
    MsgBox "Value not found!"
    Exit Sub
Else
    rng.Replace What:="OLH: Task 1 Due Date", Replacement:= _
        "DDQ Order Due Date", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
        xlReplaceFormula2
End If

 

[squeakums]

Okay, I just removed the Selection.FindNext(After:=ActiveCell).Activate - because honestly its only replacing one item so this time it doesn't error out and code works.

 

[Joe4]

Can you post your sample data, using the XL2BB tool, so I can copy your data, exactly as you have it, and test it out?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[squeakums]

Unfortunately no, my sample data is secure and I cannot load it here. Do you not have a code you have written before where I can replace the table name to delete all data and replace with a saved load process?

 

[Joe4]

Unfortunately no, my sample data is secure and I cannot load it here.

We never want you to post sensitive data here. We ask that you "dummy up" the data so that nothing sensitive is posted.

Do you not have a code you have written before where I can replace the table name to delete all data and replace with a saved load process?

Does the code I posted in my first reply not work for you?
If not, please explain how it is not working.

 

[squeakums]

Actually for this code I found the work around so I am good. Sorry for the confusion.

 

[larinda4]

Was is the exact error message you are getting?

Note that with Find/Replace, it will return an error if it does not find what you are looking for.

You can avoid this by setting the cell it is located in equal to a range variable, and checking to see if that range is empty (i.e. "Nothing"), something like this:

Dim rng As Range

Set rng = Rows("1:1").Find(What:="OLH: Task 1 Due Date", After:=Range("A1"), LookIn:= _
    xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)

If rng Is Nothing Then
    MsgBox "Value not found!"
    Exit Sub
Else
    rng.Replace What:="OLH: Task 1 Due Date", Replacement:= _
        "DDQ Order Due Date", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
        xlReplaceFormula2
End If

Hey Joe!

Me again, and again I am piggy backing on another post. Sorry about that!

Your code here works for a problem I'm encountering, however, the "If rng is Nothing Then", how would I make it just skip that part and not give a message box and exit sub?

If the value is nothing, I want it to continue down to the rest of my code. Any help would be appreciated!

 

[Joe4]

Hey Joe!

Me again, and again I am piggy backing on another post. Sorry about that!

Your code here works for a problem I'm encountering, however, the "If rng is Nothing Then", how would I make it just skip that part and not give a message box and exit sub?

If the value is nothing, I want it to continue down to the rest of my code. Any help would be appreciated!

Just remove/comments the first two lines under the IF statement out, i.e.

If rng Is Nothing Then
    'MsgBox "Value not found!"
    'Exit Sub
Else
    rng.Replace What:="OLH: Task 1 Due Date", Replacement:= _
        "DDQ Order Due Date", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
        xlReplaceFormula2
End If

 

[larinda4]

Just remove/comments the first two lines under the IF statement out, i.e.

If rng Is Nothing Then
    'MsgBox "Value not found!"
    'Exit Sub
Else
    rng.Replace What:="OLH: Task 1 Due Date", Replacement:= _
        "DDQ Order Due Date", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:= _
        xlReplaceFormula2
End If

Thanks Joe!

I messed up, this won't work for me. Thanks for taking the time though! I appreciate it.