Goto cells that do NOT contain a keyword on Excel 2007

[MANAHMAN]

Hi,

I have a simple question on Excel 2007.

I have a very very large spreadsheet e.g. 10000 rows x 10000 columns. Almost all the cells store the same value e.g. "APPLE" but a few cells contain other "unknown" values. Now, i just want to locate the cell that is not equal to "APPLE". i.e. The cursor goes to one of the cells that doesn't contain "APPLE". Conditional formatting doesn't help as spread sheet is huge. Maybe all the cells contain "Apple" or just 1 cell does not contain "Apple"

We can simply use FIND to locate "APPLE" but is there any other way to locate the cell which doesn't store "APPLE" ?

Thx.

MANAHMAN

 

[mumps]

Try this macro. Please note that "Apple" is case sensitive.

Sub test()
    Dim a As Variant, i As Long, ii As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet1").[a1].CurrentRegion
        a = .Value
        For i = LBound(a) To UBound(a)
            For ii = LBound(a, 2) To UBound(a, 2)
                If a(i, ii) <> "Apple" Then
                    Cells(i, ii).Select
                    Exit Sub
                End If
            Next ii
        Next i
    End With
End Sub

 

[Joe4]

Mumps,

Don't forget to add

Application.ScreenUpdating =True

back in at the end of your code.

Also, should mention that using CurrentRegion will only work if there are no completely blank rows or columns in the middle of the data.
If there are, you will need to find the last cell, and build the range to search baased on that.

 

[mumps]

@Joe4
Many thanks for picking up on that.

 

[MANAHMAN]

Thx both but besides of VBA, is there any other way to make it?

 

[Joe4]

Thx both but besides of VBA, is there any other way to make it?

If it was just a single column, you could use filters and say "Not Equal to Apple". But since you have thousands of columns, that won't work.
I do not know any way of doing this without VBA.

 

[MANAHMAN]

If it was just a single column, you could use filters and say "Not Equal to Apple". But since you have thousands of columns, that won't work.
I do not know any way of doing this without VBA.

Thx Joe4,

What about if i accept "not-nice" result ?

Original expected result : The cursor moves to "non-Apple" cell.
New expected result : Excel just tells me "YES" or "No" by all means. i.e. Yes - means all cells are "Apple"; No - means at least 1 cell doesn't contain "Apple". The result presentation can be anything as i just want to know Yes or No.

thx.

MANAHMAN

 

[Yongle]

Do all cells in the range except ONE contain "apple"
(other cell can either be empty or contain "pear")
Amend A1:H10 to the range containing your data and try this formula - it is not case sensitive.

=COUNTIF(A1:H10,"apple")=(ROWS(A1:H10)*COLUMNS(A1:H10))

If the cell that does not contain "apple" is empty then a simpler formula can be used

=COUNTBLANK(A1:H10)=0