validating a cell value

[22strider]

Hello Friends

I am looking for VBA code for validating a cell value by checking if it matches with entries in a column.

This code will be a part of my larger code in which I need to trap a possible error of user entering incorrect value in a cell. The code requires a user to type valid value in a cell and then press a button. But there is always a chance of user entering incorrect value. I was wondering if there was a way of checking if the cell value matches with at least one entry in the data column (which is in the same spreadsheet).

Thanks
Rajesh

 

[JoeMo]

Hello Friends

I am looking for VBA code for validating a cell value by checking if it matches with entries in a column.

This code will be a part of my larger code in which I need to trap a possible error of user entering incorrect value in a cell. The code requires a user to type valid value in a cell and then press a button. But there is always a chance of user entering incorrect value. I was wondering if there was a way of checking if the cell value matches with at least one entry in the data column (which is in the same spreadsheet).

Thanks
Rajesh

You can use a worksheet_change module for this. Here's an example where the cell you want to validate is A1 and you want the user to enter a value that matches at least one cell in the range B1:B10.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Resize(1, 1)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Not IsNumeric(Application.Match(Target.Value, Range("B1:B10"))) Then
        MsgBox "The value entered in " & Target.Address(1, 1) & " is not valid - try again"
        Application.EnableEvents = False
        With Range("A1")
            .ClearContents
            .Select
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

 

[jim may]

Small sample of your sheet -- Cell C5 is the cell to test if it is in Column A...

Excel 2010
	A	B	C
1	251
2	201
3	343
4	159
5	281		343
6	489
7	492
8	367
9	303
Sheet1

Here's the code to test...

Sub FindMyValue()
Dim LR As Long
Dim Rng As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & LR)
If WorksheetFunction.CountIf(Rng, Range("C5")) > 0 Then
MsgBox "Value found"
Else
MsgBox "Value Not Found"
End If
End Sub

 

[22strider]

Thank you; solution is simple and practical.

 

[jim may]

Tks for feedback.

 

[22strider]

The code worked fine. I just had to make small change, in row 5 I changed "A1:A" & LR to "A1":"A" & LR
without it I was getting WorksheetFunction error.

Thanks again
Rajesh