Solved: Problem with "Instr" when called with an Empty Thread

[Mackeral]

If you call "Instr" with a null argument as in "Instr("Whatever","")" you always get back a 1.

It should return a 0 indicateing that it didn't match anything.

My solution:

Function In_Str(Look_In, For_Text)    ' Correcting "Instr" function.
    ' 7/3/18 Created. WML
    
    Prog = "In_Str"
    
    If For_Text = "" Then
        In_Str = 0
    Else
        In_Str = InStr(Look_In, For_Text)
    End If
    
End Function ' In_Str()

 

[shg]

Hmm. Rattling around in my brain is a dim recollection that I've read that, but I can't duplicate it

? Range("A1:A10").Interior.Color = vbWhite returns True if all cells are white, and False otherwise
? Range("A1:A10").Interior.Color returns vbWhite if all cells are white and 0 otherwise
? Range("A1:A10").Value = 1 returns type mismatch regardless of value

Can you give an example, Rory?

A cell cannot contact the value Null.

 

[RoryA]

Try Colorindex or Text.

 

[shg]

Got it, thank you.

 

[RoryA]

I agree it's not something you'll come across often though!

 

[shg]

The value Null never appears in Excel

Oops: =SUM(A1:C1 E1:E3)