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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

guggie3

New Member
Joined
Sep 24, 2010
Messages
34
Arent you missing your continuation underscore in that section's Destination:= part??
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

teatimecrumpet

Active Member
Joined
Jun 23, 2010
Messages
307
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
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

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
Top