Posted to microsoft.public.excel.programming
|
|
"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
|