Find Throws Error 91

[Guest]

As any dedicated hacker would do, I have taken some code that works in a
macro and transplanted it. In the original code, I was popping back and forth
between WS in a single WB. This hack involves two WBs:

Windows("Copy of 2003-07 Terminations.xls").Activate
With Range("B6", Range("B" & Rows.Count).End(xlUp)) 'Assume you have
header rows
For Each rngCell In .Cells
rngCell(1, 1).Select
varName = rngCell(1, 1).Value
Selection.Copy
Windows("Job-Personal-Fed and State Taxes Trimmed-3.xls").Activate
Range("A1").Select
Cells.Find(What:=varName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

...and when I step through it to the Find statement, I get "Object variable
or With block variable not set". I read the help file on this, but couldn't
relate what it was trying to tell me to my code.

Where did I go wrong, mother dear?

 

[Guest]

This statement is wrong

With Range("B6", Range("B" & Rows.Count).End(xlUp))

should be

With Range("B6", Range("B" & Rows.Count).End(xlUp).Row)

 

[Guest]

Never mind -- what it was really trying to tell me was that it didn't find
what it was looking for. Sounds like time for "On Error".

 

[Guest]

set c = Cells.Find(What:=varName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
if not c nothing

 

[Guest]

set c = Cells.Find(What:=varName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
if not c is nothing

 

[Dave Peterson]

Maybe dropping the .select's and .activate's would make it easier to
understand--that and using a variable that represents that foundcell:

Dim TermWks as worksheet
dim TermRng as range
dim rngCell as range
Dim FedWks as worksheet
Dim FedRng as range
dim FoundCell as range

set termwks = workbooks("Copy of 2003-07 Terminations.xls") _
.worksheets("sheet999") '<--what's the name of the sheet?

set fedwks = workbooks("Job-Personal-Fed and State Taxes Trimmed-3.xls") _
.worksheets("sheet888") '<--same question here

with termWks
set termrng = .range("b6",.cells(.rows.count,"B").end(xlup))
end with

with FedWks
set fedrng = .cells 'all the sheet
'or???
set fedrng = .range("a:a") 'Just column A???
end with


for each rngcell in termrng.cells
with fedrng
set foundcell = .cells.find(what:=rngcell.value, _
after:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with

if foundcell is nothing then
msgbox "Not found!"
else
'do what you want since it was found.
end if
next rngcell

===
Watch for typos. I didn't compile or test this.

 

[Dave Peterson]

I think Joel didn't mean to include the .activate on that line.

 

[Guest]

Thank you "mother", I shall endeavor not so "sin" again <g>! Seriously, that
one fitted particularly nicely into my learning curve. Initializing the Wks
and Rng variable before starting the For Loop was something I had not been
doing in any of my Macros, hence burning up a lot of CPU energy needlessly.

 

[Susan]

dave -
given your great hacking experience (VBG LOL **JOKE!!!** **JOKE!!!**
don't shoot me! yours is probably more extensive than mine!), i am
assuming that your variables are all dim'med?

if not, switching back & forth between wbs without variables is bound
to result in excel being extremely confused, IMHO.

for instance, you don't have rngCells declared (but i guess it
wouldn't even get past that point if it wasn't dim'med earlier).

if these are indeed handled further up in your macro & you just didn't
bother posting them, then please ignore this portion of my post.

otherwise, i wonder if switching back & forth between "windows" is the
same (to excel) as switching back & forth between wbs & wkshts?? are
these 2 separate instances of excel?
just an idea
susan

 

[Susan]

now you can ignore me completely, because i didn't realize that Google
wasn't being updated (once again) & that your post had already been replied
to, several times, in a much more intelligent manner than mine.

susan

dave -
given your great hacking experience (VBG LOL **JOKE!!!** **JOKE!!!**
don't shoot me! yours is probably more extensive than mine!), i am
assuming that your variables are all dim'med?

if not, switching back & forth between wbs without variables is bound
to result in excel being extremely confused, IMHO.

for instance, you don't have rngCells declared (but i guess it
wouldn't even get past that point if it wasn't dim'med earlier).

if these are indeed handled further up in your macro & you just didn't
bother posting them, then please ignore this portion of my post.

otherwise, i wonder if switching back & forth between "windows" is the
same (to excel) as switching back & forth between wbs & wkshts?? are
these 2 separate instances of excel?
just an idea
susan

 

[Guest]

Answered, yes, and Dave Peterson's solution is the one I have adopted. So I
spent a whole hour yesterday stepping through the Macro, and then running it,
but it never got to the area for dealing with an item that didn't have the
key search term, "TER" in it. I ran it, and re-ran it -- and then suddenly it
hit me -- maybe there isn't any record that meets that condition!!!!!! I'm
re-checking it one more time this morning, but sometimes it isn't the Macro
that's "wrong"!

And your point about looking for the Dims - I didn't post them as I assumed
readers would reckon they existed. I'll behave better next time <g>!
--
Dave
Temping with Staffmark
in Rock Hill, SC

now you can ignore me completely, because i didn't realize that Google
wasn't being updated (once again) & that your post had already been replied
to, several times, in a much more intelligent manner than mine.

susan

 

[Guest]

P.S. -- I've been hacking since dBase III+ in 1986!
--
Dave
Temping with Staffmark
in Rock Hill, SC

now you can ignore me completely, because i didn't realize that Google
wasn't being updated (once again) & that your post had already been replied
to, several times, in a much more intelligent manner than mine.

susan

 

[Susan]

you beat me. i wasn't "into" computers back then.
susan

 

[Guest]

Ah, well, but I got a true perspective one day when I was doing some Computer
Engineering in Austin, TX, and one of the full time employees there told me
that he had worked on ENIAC in 1949! (I think that was the year).