Object Required Error

[MarkHear1]

Hi All,

I have written the code below and can see no reason for it not to
work, however when I run the code I get an error saying "Object
Required" I don't understand why I am seeing this error, however if i
change cell.entirerow.delete to cell.interior.color=vbBlue then the
code will run fine.
Can anybody offer any suggestions?

Sub A_1_Macro()
'UserForm1.Show

Application.ScreenUpdating = False

For x = 0 To 4

For Each cell In Range("A1:A35000")

If Mid(cell, 26, 2) = " 0" Then
cell.EntireRow.delete
End If

If Mid(cell, 1, 18) = "CR INFO: Executing" Then
cell.EntireRow.delete
End If

If cell.Value = "MADD - SARM exception statistics" Then
cell.EntireRow.delete
End If

If cell.Value = "Terminated from far-end" Then
cell.EntireRow.delete
End If

Next cell

Next x

Call A_2_DeleteRows

End Sub

Many Thanks,
Mark

 

[Guest]

Hi Mark,

Just guessing your achievement is identify cell which containing:
"CR INFO: Executing, 0,MADD - SARM exception statistics,Terminated from
far-end"

so you can use it:

Sub A_1_Macrox()
Dim Strx, cell As Range, x As Integer
Strx = Split("CR INFO: Executing, 0,MADD - SARM exception
statistics,Terminated from far-end", ",")

For x = 0 To UBound(Strx)
For Each cell In Range("A1:A500")
If InStr(1, cell, Strx(x)) Then cell.EntireRow.Delete
Next cell
Next x

End Sub

 

[MarkHear1]

Hi Mark,

Just guessing your achievement is identify cell which containing:
"CR INFO: Executing, 0,MADD - SARM exception statistics,Terminated from
far-end"

so you can use it:

Sub A_1_Macrox()
Dim Strx, cell As Range, x As Integer
Strx = Split("CR INFO: Executing, 0,MADD - SARM exception
statistics,Terminated from far-end", ",")

For x = 0 To UBound(Strx)
For Each cell In Range("A1:A500")
If InStr(1, cell, Strx(x)) Then cell.EntireRow.Delete
Next cell
Next x

End Sub
--

Regards,

Halim



















- Show quoted text -

Halim,

Thank you for your suggestion, but can anybody offer an suggestions as
to why my code is not working?

Thanks,

Mark

 

[MarkHear1]

Hi Mark,

Just guessing your achievement is identify cell which containing:
"CR INFO: Executing, 0,MADD - SARM exception statistics,Terminated from
far-end"

so you can use it:

Sub A_1_Macrox()
Dim Strx, cell As Range, x As Integer
Strx = Split("CR INFO: Executing, 0,MADD - SARM exception
statistics,Terminated from far-end", ",")

For x = 0 To UBound(Strx)
For Each cell In Range("A1:A500")
If InStr(1, cell, Strx(x)) Then cell.EntireRow.Delete
Next cell
Next x

End Sub
--

Regards,

Halim



















- Show quoted text -

Thank you for your suggestion Halim.

However, can anybody please offer any suggestions as to why the code I
posted didn't work?

Thanks,

Mark

 

[Guest]

Mark,

1. You can run your macro in debug mode by pressing F8 in your sub
so you can found the error line highlighted yellow
2. and check whether sub A_2_DeleteRows is available in your project...

 

[Guest]

I think you are doing something like this:


Sub NoObject()

Set cell = Cells(1, 1)
cell.Value = "some value"

MsgBox cell.Value

Cells(1, 1).EntireRow.Delete

'you get an error here because
'you've just deleted the cell
'you are trying to use

MsgBox cell.Value

End Sub


If one of your ifs is succesful:

If Mid(cell, 26, 2) = " 0" Then
cell.EntireRow.delete
End If

cell gets deleted, but you try to use it in the next if:

If Mid(cell, 1, 18) = "CR INFO: Executing" Then ...



You can try Halim's suggestion or to combine your ifs into one:


If Mid(cell, 26, 2) = " 0" Or _
Mid(cell, 1, 18) = "CR INFO: Executing" Or _
cell.Value = "MADD - SARMA exception statistics" Or _
cell.Value = "Terminated from far-end" Then
'do something
End If

 

[MarkHear1]

I think you are doing something like this:

Sub NoObject()

Set cell = Cells(1, 1)
cell.Value = "some value"

MsgBox cell.Value

Cells(1, 1).EntireRow.Delete

'you get an error here because
'you've just deleted the cell
'you are trying to use

MsgBox cell.Value

End Sub

If one of your ifs is succesful:

If Mid(cell, 26, 2) = " 0" Then
cell.EntireRow.delete
End If

cell gets deleted, but you try to use it in the next if:

If Mid(cell, 1, 18) = "CR INFO: Executing" Then ...

You can try Halim's suggestion or to combine your ifs into one:

If Mid(cell, 26, 2) = " 0" Or _
Mid(cell, 1, 18) = "CR INFO: Executing" Or _
cell.Value = "MADD - SARMA exception statistics" Or _
cell.Value = "Terminated from far-end" Then
'do something
End If

--
urkec





- Show quoted text -

Urkec,

Thank you, that makes sense now, i obviously lack common sense as well
as VBA Knowledge!

Thanks Again,

Mark

 

[ilia]

Sub A_1_Macro()
Dim myCell as Excel.Range
Dim myRange as Excel.Range

set myRange = ActiveSheet.Range("A1:A35000")

for each myCell in myRange

' your code

next myCell

set myCell = Nothing
set myRange = Nothing
end sub