"Object variable not set"??
 

I'm using Find in:

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long

<< other stuff

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

If Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

The IF statement generates the "Object variable or With block variable not
set" error. But I use this same code in another macro as:

ws1.Activate
rngSearch.Select
If Selection.Find(What:=strTIR _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then
(In this case, there are two workbooks, and I have to activate the one I
want to search. In the first code, I am using only one workbook.)

Any suggestions?
Ed

"Object variable not set"??
 

Ed,

When using Find, you should write your code as follows:

Dim FoundCell As Range
Set FoundCell = Selection.Find(...)
If Not FoundCell Is Nothing Then
' text found, do something with FoundCell
Else
' text not found, handle it
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
I'm using Find in:

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long

<< other stuff

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

If Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

The IF statement generates the "Object variable or With block
variable not
set" error. But I use this same code in another macro as:

ws1.Activate
rngSearch.Select
If Selection.Find(What:=strTIR _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then
(In this case, there are two workbooks, and I have to activate
the one I
want to search. In the first code, I am using only one
workbook.)

Any suggestions?
Ed

"Object variable not set"??
 

Chip, my code now looks like:

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

Set rngFound = Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate

If rngFound Is Nothing Then
Range("A" & thisRow).EntireRow.Hidden = True
End If

I still get the "Object variable not set" error on the Set rngFound
statement???
Ed

"Chip Pearson" wrote in message
...
Ed,

When using Find, you should write your code as follows:

Dim FoundCell As Range
Set FoundCell = Selection.Find(...)
If Not FoundCell Is Nothing Then
' text found, do something with FoundCell
Else
' text not found, handle it
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
I'm using Find in:

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long

<< other stuff

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

If Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

The IF statement generates the "Object variable or With block
variable not
set" error. But I use this same code in another macro as:

ws1.Activate
rngSearch.Select
If Selection.Find(What:=strTIR _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then
(In this case, there are two workbooks, and I have to activate
the one I
want to search. In the first code, I am using only one
workbook.)

Any suggestions?
Ed

"Object variable not set"??
 

Ed,

Get rid of the Activate at the end of the Find line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
Chip, my code now looks like:

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

Set rngFound = Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate

If rngFound Is Nothing Then
Range("A" & thisRow).EntireRow.Hidden = True
End If

I still get the "Object variable not set" error on the Set
rngFound
statement???
Ed

"Chip Pearson" wrote in message
...
Ed,

When using Find, you should write your code as follows:

Dim FoundCell As Range
Set FoundCell = Selection.Find(...)
If Not FoundCell Is Nothing Then
' text found, do something with FoundCell
Else
' text not found, handle it
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
I'm using Find in:

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long

<< other stuff

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

If Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

The IF statement generates the "Object variable or With
block
variable not
set" error. But I use this same code in another macro as:

ws1.Activate
rngSearch.Select
If Selection.Find(What:=strTIR _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then
(In this case, there are two workbooks, and I have to
activate
the one I
want to search. In the first code, I am using only one
workbook.)

Any suggestions?
Ed

"Object variable not set"??
 

Thanks, Chip! That was it!

Ed

"Chip Pearson" wrote in message
...
Ed,

Get rid of the Activate at the end of the Find line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
Chip, my code now looks like:

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

Set rngFound = Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate

If rngFound Is Nothing Then
Range("A" & thisRow).EntireRow.Hidden = True
End If

I still get the "Object variable not set" error on the Set
rngFound
statement???
Ed

"Chip Pearson" wrote in message
...
Ed,

When using Find, you should write your code as follows:

Dim FoundCell As Range
Set FoundCell = Selection.Find(...)
If Not FoundCell Is Nothing Then
' text found, do something with FoundCell
Else
' text not found, handle it
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ed" wrote in message
...
I'm using Find in:

Dim MyTarget As String
Dim rngSearch As Range
Dim thisRow As Long

<< other stuff

thisRow = ActiveCell.Row
Set rngSearch = Range("A" & thisRow & ":AA" & thisRow)
rngSearch.Select

If Selection.Find(What:=MyTarget _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

The IF statement generates the "Object variable or With
block
variable not
set" error. But I use this same code in another macro as:

ws1.Activate
rngSearch.Select
If Selection.Find(What:=strTIR _
, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, _
MatchCase:=False).Activate Then
(In this case, there are two workbooks, and I have to
activate
the one I
want to search. In the first code, I am using only one
workbook.)

Any suggestions?
Ed