Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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? -- Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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) "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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". -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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 "Dave Birley" wrote: 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". -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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 Birley" wrote: 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". -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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 Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
I think Joel didn't mean to include the .activate on that line.
Joel wrote: 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 Birley" wrote: 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". -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Peterson" wrote: 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 Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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 On May 16, 12:15 pm, Dave Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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" wrote in message ps.com... 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 On May 16, 12:15 pm, Dave Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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 "Susan" wrote: 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" wrote in message ps.com... 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 On May 16, 12:15 pm, Dave Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
P.S. -- I've been hacking since dBase III+ in 1986!
-- Dave Temping with Staffmark in Rock Hill, SC "Susan" wrote: 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" wrote in message ps.com... 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 On May 16, 12:15 pm, Dave Birley wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
:)
you beat me. i wasn't "into" computers back then. susan On May 17, 10:13 am, Dave Birley wrote: P.S. -- I've been hacking since dBase III+ in 1986! -- Dave Temping with Staffmark in Rock Hill, SC |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Throws Error 91
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). -- Dave Temping with Staffmark in Rock Hill, SC "Susan" wrote: :) you beat me. i wasn't "into" computers back then. susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook.open throws 1004 error | Excel Programming | |||
How do I perform a certain function if VBA throws up an error? | Excel Programming | |||
[Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) | |||
Format statement no longer throws an error | Excel Programming |