CODEWITHSUNDEEP
excelvbarange
Methexis
Methexis

Reputation: 2861

VBA Identify numbers within a range

I am sure this is a simple one but cant seem to find anything explicit out on the WWW.

I need my code to identify any numbers between 1 and 9 within a column for it to throw the error back for that range, I can only get it working with one number at present. 

Public Sub ErrorCheck()
Dim FindString As String
Dim Rng As Range
FindString = ""
If VBA.Trim(FindString) <> "" Then
With Sheets("Scoring").Range("S:S")
    Set Rng = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    If Not Rng Is Nothing Then
        MsgBox "Error", True
    Else

    End If
End With
End If
End Sub

Thanks for your help!

Upvotes: 1

Views: 2381

Answers (1)

user2480047
user2480047

Reputation:

You can execute your code iteratively for all the values you want. Example:

Public Sub ErrorCheck()
    Dim FindString As String
    Dim Rng As Range
    Dim startVal As Integer, endVal As Integer

    startVal = 1
    endVal = 9

    For i = startVal To endVal
        FindString = CStr(i)
        With Sheets("Scoring").Range("S:S")
            Set Rng = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
            If Not Rng Is Nothing Then
                MsgBox "Error", True
                Exit For
            Else

            End If
        End With
    Next i

End Sub

Upvotes: 1

Related Questions