object Variable or with block not set

teatimecrumpet

Active Member
Joined
Jun 23, 2010
Messages
307
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(foundTwo.Offset(1, 0), foundTwo.Offset(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
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
here is a snippet of using error checks
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top