ExcelBanter - analyze list

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - analyze list (https://www.excelbanter.com/excel-worksheet-functions/138743-analyze-list.html)

I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex

One way is to use Data validation. Select B1, then Data | Validation.
In the Settings tab select List from the drop down, then highlight the
parts of column A where your data is (eg A1:A20). You can specify an
Input message if you want (eg "Please enter a number") by clicking the
Input Message tab, and on the Error Alert tab you can select Type (eg
STOP) with a message "Value not in list".

Another way is to allow the User to enter data in B1, and put this
formula in C1:

=IF(ISNA(VLOOKUP(B1,A1:A20,1,0)),"Not in list","Value is in the list")

Hope this helps.

Pete

On Apr 12, 2:55 pm, "alex" wrote:
I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex

Try the following procedure. Put it in the worksheet change event of the
appropriate worksheet. In this procedure cell C1 is where the user will
enter their value. If the value entered exists in column A then the program
will also place the value in cell B1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range, X As Range, Y As Range
Set X = ActiveSheet.Range("C1")
Set Isect = Application.Intersect(Target, X)
If Not Isect Is Nothing Then
For Each Y In ActiveSheet.Range("A:A").Cells
If Y.Value = X.Value Then
Application.EnableEvents = False
ActiveSheet.Range("B1").Value = X.Value
Application.EnableEvents = True
GoTo EndLine:
End If
Next Y
EndLine:
End If
End Sub

Hope this is a start.

Bill Horton

"alex" wrote:

I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex

On Apr 12, 10:40 am, "Pete_UK" wrote:
One way is to use Data validation. Select B1, then Data | Validation.
In the Settings tab select List from the drop down, then highlight the
parts of column A where your data is (eg A1:A20). You can specify an
Input message if you want (eg "Please enter a number") by clicking the
Input Message tab, and on the Error Alert tab you can select Type (eg
STOP) with a message "Value not in list".

Another way is to allow the User to enter data in B1, and put this
formula in C1:

=IF(ISNA(VLOOKUP(B1,A1:A20,1,0)),"Not in list","Value is in the list")

Hope this helps.

Pete

On Apr 12, 2:55 pm, "alex" wrote:

I'm trying to analyze a list in Excel but I need some help.

I have list of values in column A (let's say, e.g., the numbers 1
through 100).

I want to compare a single value against the list by creating a box
where the user types a value and Excel checks that value against the
list. If the value is not on the list, a msg box displays such; if
the value is on the list, a msg box displays such and adds the value
to the next column.

E.g.:
A user opens Excel...
In column A are the values 1 to 100.
Somewhere to the right of column A is a txt box or other control where
the user can type a value.
If, the user types '231' a msg box displays 'value not on the list.'
If, the user types '44' a msg box displays 'value is on the list,'
then inserts the number 44 in cell B1.

Any thoughts? Thanks for any help.

alex- Hide quoted text -

- Show quoted text -

Thanks for the help Pete; I'll give it a try.

alex