If Not... Then Nothing

[GreenyMcDuff]

Hi guys,

Just looking for some information here out of curiosity and to expand my knowledge.

I use the If Not... Then Nothing statement a lot to set the true/false of a variable e.g:

X = False
    Set Xtinct = Range("1:1").Find("Status")
    If Not Xtinct.EntireColumn.Find("Extinct") Is Nothing Then X = True

Why is this code used this way round? is it not possible to say if variable exists then set the variable to true?

 

[Andrew Poulsom]

If the text is not found, Find returns Nothing. If the text is found, Find returns a range object, which is not Nothing. You aren't testing for the existence of a variable. You are testing what's returned by Find.

 

[GreenyMcDuff]

Thanks Andrew.

So if I wanted to I could write this statement as:

X = True
    Set Xtinct = Range("1:1").Find("Status")
    If Not Xtinct.EntireColumn.Find("Extinct") Is Not Nothing Then X = False

and it would do the same thing?

 

[RoryA]

Too many Nots:

If Not Xtinct.EntireColumn.Find("Extinct") Is Nothing Then X = True

or:

X = Not Xtinct.EntireColumn.Find("Extinct") Is Nothing

 

[GreenyMcDuff]

Ok thanks Rory,

A slightly different question now.

How does the count command work in excel?

I have this code:

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

It counts accurately the number of rows of data in a worksheet even if there are more rows in column B than A. How does it work?

 

[RoryA]

It counts accurately the number of rows of data in a worksheet even if there are more rows in column B than A. How does it work?

There must be something in the corresponding rows for A if it is returning the correct number in those circumstances.

 

[GreenyMcDuff]

Yeah you are right, there are spaces in them. All makes sense now, thanks Rory and Andrew.