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.
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
Last edited: