Run-time error 1004 - No cells were found

[excelnewbie1986]

Hi all,

Hoping someone can help! When running the macro below, I am getting a run-time error 1004 - No cells were found error. I have tried diagnosing this issue myself, but am at a loss. This macro worked perfectly on the worksheet it was originally created on. So I used that worksheet as a template, and I then paste a tonne of raw data into the template to run the macro to remove certain things, but now when I run the macro in subsequent copied versions of the template, it no longer works. The blue text denotes where the debugger has picked up the error.

Application.ScreenUpdating = False
With Range("Z:Z")
.Replace "G", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Application.ScreenUpdating = False
With Range("V:V")
.Replace "WATERS EDGE PRIMARY SCHOOL", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Application.ScreenUpdating = False
With Range("V:V")
.Replace "Green Abbeys", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "331", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "860", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("W:W")
.Replace "", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

With Range("Y:Y")
.Replace "", "=GGG", xlWhole, , False, , False, False
.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With

Thanks in advance!!

 

[mole999]

probably doesn't know what sheet it should be working on so

sheets("your sheet name"). then range

 

[Fluff]

When you get the error have a look at col Z on the active sheet. Do you have any cells saying #NAME ?
If not there are no cells that contain "G"

 

[excelnewbie1986]

When you get the error have a look at col Z on the active sheet. Do you have any cells saying #NAME ?
If not there are no cells that contain "G"

Hello again!

Prior to running the macro, there are values of 'G' in column Z. When I run the macro and it produces the runtime error, when I click OK, the values of 'G' have disappeared from column Z, but the values in other columns remain. I'm guessing it's because the macro doesn't finish running?

 

[excelnewbie1986]

And there are no cells that say #NAME either.

 

[Fluff]

When you get the error click Debug, which line of code is highlighted in yellow?

 

[excelnewbie1986]

Line 17.

 

[Fluff]

Can you please post the section of code, saves me counting lines

 

[mole999]

Line 17.

Ok without the full sub, its difficult to work out what line 17 looks like

 

[excelnewbie1986]

Sorry both. I had pasted the code into the original post, but not the whole sub......so it's line 17 for me, but line 4 (highlighted blue) below. Not only am I trying to get used to Excel, but forums too

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("Z:Z")[/COLOR]
[COLOR=#333333].Replace "G", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#0000ff].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("V:V")[/COLOR]
[COLOR=#333333].Replace "WATERS EDGE PRIMARY SCHOOL", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]With Range("V:V")[/COLOR]
[COLOR=#333333].Replace "Green Abbeys", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "331", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "860", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("W:W")[/COLOR]
[COLOR=#333333].Replace "", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]With Range("Y:Y")[/COLOR]
[COLOR=#333333].Replace "", "=GGG", xlWhole, , False, , False, False[/COLOR]
[COLOR=#333333].SpecialCells(xlFormulas, xlErrors).EntireRow.Delete[/COLOR]
[COLOR=#333333]End With[/COLOR]