Run Time Error 91 - VBA is Wrong, I'm Right in Ignoring it...or Am I?

[Tash Point O]

Hi,

I'm back for the 2nd day in a row seeking help . I'm certain the Run Time Error 91 I am receiving while trying to create a loop is VBA just being prissy. I used a nifty error handling code that a member gave me yesterday and I was about to bid the error good riddance but now my OCD is making me want to solve the problem.

Let me have it if I'm wrong and VBA is right in pointing out an error:

 

[NdNoviceHlp]

Care to share what you're actually trying to achieve? Dave

 

[Tash Point O]

Care to share what you're actually trying to achieve? Dave

Yes!
In Range A:A, I’d like to find a certain word found in certain cells and once found, clear contents for that entire row. Since there are multiple instances of these cells that contain this wors, I need to loop (or perhaps there’s a better method). My code works except for the pesky error message that i get when i loop.

 

[Michael M]

This would be far quicker....UNTESTED...no selecting required

Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp)) '.Offset(, 5)
    .Replace "Deal" & Chr(10) & "ID", "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.ClearContents
  End With
End Sub

 

[Tash Point O]

Hi!

When I run your code I get a Run Timer 1004 - No cells were found error.

 

[Fluff]

Try changing this

.Replace "Deal" & Chr(10) & "ID", "#N/A", [COLOR=#ff0000]xlPart[/COLOR], , False, , False, False

 

[Tash Point O]

Hello MrExcelMVP!

This too doesn't work, same error.

 

[Fluff]

Try

Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp)) 
   .Replace "*Deal" & Chr(10) & "ID*", "#N/A", xlPart, , False, , False, False
   .SpecialCells(xlConstants, xlErrors).EntireRow.ClearContents
  End With
End Sub

 

[Tash Point O]

You have MVP in your name because it's no lie! Thank you, that worked!

I tried getting rid of the offset before and it didn't work, maybe i missed something. If you have a moment, can you tell me why a with loop is better than a for loop in this case?

 

[Fluff]

Glad it's sorted & thanks for the feedback.

That code doesn't actually use a loop, a With statement is simply a form of "shorthand". This is basically the same but without the With statement

Sub MM1()
   Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "*Deal" & Chr(10) & "ID*", "#N/A", xlPart, , False, , False, False
   Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants, xlErrors).EntireRow.ClearContents
End Sub