asked on

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

Andrew Porter

Are (6, 14) the proper cells?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

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

John Carney

ASKER

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

John Carney

ASKER

Thanks again.

- John