object Variable or with block not set

[teatimecrumpet]

Hi,

I'm getting the "Object Variable or with block not set" error. With my code (below). I t fails on the part that is highlighted purple which I know would exist if the word participant isn't located. But how would I it skip the error if it isn't found?

I tried to use the "On Error GoTo terminate" "terminate:" but it didn't work.

Thanks in advance.

Sub Macro1()

Dim wb As Workbook
Dim ws As Worksheet
Dim AW As String
Set wb = ActiveWorkbook
Dim foundOne As Range
Dim foundTwo As Range
Dim foundthree As Range

For Each ws In wb.Worksheets
If ws.Range("A7").Value <> "Purple" Then
Select Case LCase(ws.Name)
'Sheet names to exclude from formatting
Case Is = "blue", "green", "yellow"
Case Else
'formatting and pasting
'one
Set foundOne = ws.Range("C:C").Find(What:="Name", After:=ws.Range("C1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If foundOne.Row > 1 Then
'Range(ws.Range("C1"), foundOne.Offset(-1, 0)).EntireRow.Delete shift:=xlUp
Range(foundOne.Offset(1, 0), foundOne.Offset(1, 1).End(xlDown)).Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
End If

'two
Set foundTwo = ws.Range("E:E").Find(What:="Name", After:=ws.Range("E1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If foundTwo.Row > 1 Then
Range(foundTwffset(1, 0), foundTwffset(1, 1).End(xlDown)).Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
End If
'three

Set foundthree = ws.Range("G:G").Find(What:="Name", After:=ws.Range("G1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If foundthree.Row > 1 Then
Range(foundthree.Offset(1, 0), foundthree.Offset(1, 1).End(xlDown)).Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
End If
End Select
Else
ws.Rows("1:1").Font.Bold = True
End If
Next ws

End Sub

 

[guggie3]

Arent you missing your continuation underscore in that section's Destination:= part??

 

[texasalynn]

here is a snippet of using error checks

On Error Resume Next
For i = 2 To LR
    Cells(i, 1).Activate
    
    Set rFound = Rows(i).Find(What:="@", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If rFound Is Nothing Then GoTo 0
    
    rFound.Activate
    
    ActiveCell.Cut Destination:=Cells(i, 27)
    On Error GoTo 0
0

 

[teatimecrumpet]

Thanks Tex! Do you know why putting a "On Error GoTo terminate" "terminate:" around the bit of code that satisfies the "Case Else" didn't work for me?

Guggie, I think it just pastes funny.



For anyone else here's what my code looks like now:

'three
On Error Resume Next
Set foundthree = ws.Range("G:G").Find(What:="Participant", After:=ws.Range("G1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If foundthree.Row > 1 Then
Range(foundthree.Offset(1, 0), foundthree.Offset(1, 1).End(xlDown)).Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
End If
On Error GoTo 0
0
End Select
Else
ws.Rows("1:1").Font.Bold = True
End If
Next ws