vba - find non-matching value

[MisterProzilla]

Hi,

I want to be able to find a non-matching value in VBA.

So, for example, this code finds the column of the first cell containing a number 1:

Sheet4.Range("D4:N4").find(1, searchdirection:=xlNext).Column

I'm looking for a way to change it so that it finds the column of the first value that is not 1.

Does anyone know how to do this?

 

[Yongle]

Here is one method (using Range.Find)
- this ignores empty cells and finds the first value not equal to Test

Sub FindNotInColumn()
    On Error Resume Next
    Dim cel As Range, addr As String, Test As String
    [COLOR=#000080]Test [/COLOR]= 1
    
    With Sheet4.Range("D4:N4")
        Set cel = .Find("*", LookIn:=xlValues)
        If Not cel Is Nothing Then
            addr = cel.Address
            If CStr(cel) <> Test Then GoTo TheEnd
            Do
                Set cel = .FindNext(cel)
                If CStr(cel) <> Test And cel.Address <> addr Then GoTo TheEnd
            Loop While Not cel Is Nothing And cel.Address <> addr
        End If
    End With
    MsgBox "nothing found"
    Exit Sub
TheEnd:
    MsgBox "value " & cel & vbCr & "found in column " & cel.Column, , "Non-match for " & Test
End Sub

 

[Yongle]

or (again ignoring empty cells)

Sub FindNonMatch()
    Dim cel As Range, Test As String
    [COLOR=#000080]Test [/COLOR]= 1
    For Each cel In Sheet4.Range("D4:N4")
        If cel > "" And CStr(cel) <> Test Then
            MsgBox cel.Column
            End
        End If
    Next
    MsgBox "nothing found"
End Sub

 

[MisterProzilla]

I was hoping that there would be a simple operator you'd insert to specify that it's a non-match you're looking for, like using "<>"& in COUNTIF formulae, for example. Maybe not though.

It looks like using a loop is the way to go, but I need to search outwards from a selected cell, so need to loop both backwards and forwards. Do you know of a way to loop backwards, is it step -1 or something?

 

[Yongle]

You can go backwards with Range.Find
- using constant xlPrevious instead of xlNext

There are many ways to achieve what you want

In order that I can help you further, please you explain more precisely (ie with some detailed examples) what you are trying to achieve?
- Which range is being searched? (How many rows? How many columns?)
- What is a typical value being searched for? (Is it always numeric? Is it always an integer? Is it always positive?)
- What does searching backwards and forwards mean? (Are you looking for 2 results each time? etc)
- Is the found column number always the value to be returned?

thanks

 

[pgc01]

Hi

This is a not efficient but very simple function that returns the cell not equal to target, searching forward or backward:

' finds a cell whose value is different from target, forward or backward
Function FindFB(r As Range, vTarget As Variant, bForward As Boolean) As Range
Dim rc As Range

For Each rc In r
    If rc.Value <> vTarget Then
        Set FindFB = rc
        If bForward Then Exit Function
    End If
Next rc
End Function

 

[Scott Huish]

Sub Test()
MsgBox Evaluate("MIN(IF(D4:N4<>1,COLUMN(D4:N4)))")
End Sub