Get an error only the second time when it's executed ...

[Goppi]

Quit a tricky question:
Why does following code end up with an error 91 the second time the find command is executed ?

Note:The data is sorted whereas all "N/A" are at the very beginning and "0" is a the end of the list.

nRow = Columns(1).Find(What:="N/A", After:=Cells(nLastDataRow, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(Rows(1), Rows(nRow)).Hidden = True
nRow = Columns(1).Find(What:="0", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Range(Rows(nRow), Rows(nLastDataRow)).Hidden = True


Does anybody have an idea ?

cheers,

Goppi

 

[TonyJollans]

Hi Goppi,

After you have found your last "N/A" you hide all the rows which contain it. When you try to find it again it is not found because Find only looks in visible rows. Because the Find has failed it cannot return a Range object and so you cannot get the Row property.

The error 91 means the property (.Row) doesn't exist and the reason it doesn't exist is because the object (the range that Find returns) doesn't exist.

Enjoy,
Tony

 

[Goppi]

Tony,

Thanks for the 91 error explanation - However that didn't solve my problem: The strange thing is that in the second "Find" line I try to find the first row with a "0" in it and this can't be found for any funny reason. The cells themselve contain as a value a "0", but are being displayed with a percentage format... - I just tried to use "0.00%" which works for my big surprise. So why does a Cells(x.y).value return a "0" when the Function Find needs even with the setting xlValue the formatted "0.00%" ???

Goppi

 

[TonyJollans]

Hi Goppi,

I answered as I did because that was what I thought the question must mean after I tried it and didn't get the problem until I ran it a second time through. Now you explain further I have gone back and tried it and cannot re-create what you get. Nor can I explain it, I'm afraid. But I'm glad you've got it working.

Enjoy,
Tony

 

[Goppi]

Thanks Tony,

I've actually written another thread related to this topic. Just because I ran into a similiar problem and also because the subject was not related anymore.

Please have a look at the new thread where I explained the circumstances in a different way.

Peter