none
find a value in a range RRS feed

  • Question

  • I have a range of 100 cells in a single column.

    Valid values for each cell is 0 or 7. Invalid values will be between 1-6 inclusive.

    I want to find some VBA code which will find the first invalid value.

    I know I can loop round this but I was looking fro something a bit slicker - but so far found nothing.

    any ideas?

    thank you.

    Monday, October 12, 2015 3:42 PM

Answers

  • Valid values for each cell is 0 or 7. Invalid values will be between 1-6 inclusive.

    I want to find some VBA code which will find the first invalid value.

    Sub Test()
      Dim All As Range, This As Range, First As Range
      Dim i As Integer
      
      'setup your cells with the values
      Set All = Range("A1:A1000")
      
      For i = 1 To 6
        'Find 1..6
        Set This = All.Find(i, LookIn:=xlValues, LookAt:=xlWhole)
        'Found?
        If Not This Is Nothing Then
          If First Is Nothing Then
            'First result
            Set First = This
          Else
            'Current cell above the last occurence?
            If This.Row < First.Row Then Set First = This
          End If
        End If
      Next
      
      If Not First Is Nothing Then
        MsgBox "Found " & First.Value & " in " & First.Address(0, 0)
      Else
        MsgBox "Not found"
      End If
    End Sub
    

    • Marked as answer by py1 Tuesday, October 13, 2015 12:56 PM
    Tuesday, October 13, 2015 10:12 AM

All replies

  • Without looping through the records, I suppose you could use Ctrl+F (find) or filter the array.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, October 12, 2015 6:47 PM
  • Hi py1,

    Since there are many types of data validation, it seems that you need to loop the cells and check Validation.Value property.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 13, 2015 8:41 AM
    Moderator
  • Hi Starain

    Thanks for your answer.

    As I understand it , the Validation.Value property can be used to set validation values which restrict user input.

    Unfortunately this is not what I am looking for.

    The  values in the range are set by a formula in the cells - valid values are 0, 7 ; invalid are 1-6 ; 

    I want to be able to identify if any of the cells in the range have an invalid value in the cells ; does that make sense?

    many thanks   

    Tuesday, October 13, 2015 9:08 AM
  • Valid values for each cell is 0 or 7. Invalid values will be between 1-6 inclusive.

    I want to find some VBA code which will find the first invalid value.

    Sub Test()
      Dim All As Range, This As Range, First As Range
      Dim i As Integer
      
      'setup your cells with the values
      Set All = Range("A1:A1000")
      
      For i = 1 To 6
        'Find 1..6
        Set This = All.Find(i, LookIn:=xlValues, LookAt:=xlWhole)
        'Found?
        If Not This Is Nothing Then
          If First Is Nothing Then
            'First result
            Set First = This
          Else
            'Current cell above the last occurence?
            If This.Row < First.Row Then Set First = This
          End If
        End If
      Next
      
      If Not First Is Nothing Then
        MsgBox "Found " & First.Value & " in " & First.Address(0, 0)
      Else
        MsgBox "Not found"
      End If
    End Sub
    

    • Marked as answer by py1 Tuesday, October 13, 2015 12:56 PM
    Tuesday, October 13, 2015 10:12 AM
  • Andreas

    Nice solution - thank you.

    Peter

    Tuesday, October 13, 2015 12:56 PM