Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Unexpected 'Object variable or With block variable not' error

Please look at the two code snippets below. The script containing the first one (Column D) executes perfectly. But when I step htrough the second one, I get an'Object variable or With block variable not set' error. Why is that, and how do I fix it?

Thanks,
John
THIS ONE WORKS:
Dim top As Range, btm As Range
Set top = [D6].EntireRow
Set btm = Columns("D:D").Find(What:=[D5].Value, After:=Cells(6, 4), LookIn:=xlValues, LookAt:=xlWhole).Offset(-1, 0)

THIS ONE JAMS ON THE LAST LINE:
Dim top As Range, btm As Range
Set top = [N6].EntireRow
Set btm = Columns("N:N").Find(What:=[N5].Value, After:=Cells(6, 14), LookIn:=xlValues, LookAt:=xlWhole).Offset(-1, 0)

Open in new window

0
gabrielPennyback
Asked:
gabrielPennyback
2 Solutions
 
andeporterCommented:
Are (6, 14) the proper cells?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is that if Find() does not find anything, it returns Nothing... so, Offset(..) won't work.

you have to do in 2 steps:


Dim top As Range, btm As Range, f as range
Set top = [N6].EntireRow
set f = Columns("N:N").Find(What:=[N5].Value, After:=Cells(6, 14), LookIn:=xlValues, LookAt:=xlWhole)
if not (f is nothing) then
  Set btm = f.Offset(-1, 0)


end if
0
 
gabrielPennybackAuthor Commented:
Hi angel, your code kills the error but the macro doesn't execute. But what I don't understand at all is this: Why does the first macro work just fine as it is?
Just to recap, N5 has the value "10/2/2009" That value occurs first in N79. I even tried hard coding the value in N5. I thought pehaps the fact that its value was the result of a formula was affecting things. Although, again, everything works just fine in the Column D version.  ????
Thanks,
John
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will repeat my first sentence:

the problem is that if Find() does not find anything, it returns Nothing... so, Offset(..) won't work.

so, the explanation is this: column D contains what you are looking for, column N does not.
0
 
Dave BrettCommented:
If you do expect the value in N to be found (as per A3's comment the code is returning nothing) then you may be comparing differnt strings - ie a text date as a string to a serial number  (ie todays data is seen bty Excel as 40614)

Is N5 entered on the same basis as the rest of column N?

Cheers

Dave
0
 
gabrielPennybackAuthor Commented:
Ahh .. interesting. Column N has dates with times (10/1/2009  10:45:00 PM), and I thought that rounding down and changing the format to "mmm d" would do the trick, which it didn't. But your last post got me to consider changing the formatting to General, and now it works great.
Thanks,
John
0
 
gabrielPennybackAuthor Commented:
Thanks again.

- John
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now