Search the value in the column.

[shanu]

Hi

I want a function , whick takes the value from a input textbox and compare with the values of a particular column(up to 10 rows).If any of the value in that column matches with this input text box value, then it should return that input textbox value. Please help me..

[mikerickson]

To where should this value be returned?

[shanu]

i want that input textbox value and cell(row,column) and it should return while click any button.

[mikerickson]

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = UserForm1.TextBox1.Text
End Sub

[mikerickson]

.

[shanu]

mike, thank for ur intative to help.But my answer was not that one.can u read that again.

Actually I have some values already in that column.When i give a value in that textbox , it should check wheter that value is there in that column.If it is there it should return the cell(row,column) where that value has find in the column. Other wise it should show a mesage " Your input value is not able find in the column"

Sorry to make you confusion.

[mikerickson]

Private Sub CommandButton1_Click()
Dim rowNumber As Variant
rowNumber = Application.Match(Me.TextBox1.Value, ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0)
If IsError(rowNumber) Then
MsgBox Me.TextBox1.Value & " is not in column A."
Else
MsgBox Me.TextBox1.Value & " is in row " & CStr(rowNumber) & " of column A."

End If
End Sub

[shanu]

Hurray!!!!!..It works :friends:

Thanks mike for your valuable time and effort.

But it not works for integers in excel.

[mikerickson]

That's a good problem!

If your data is numeric, substituting this line

rowNumber = Application.Match(Val(Me.TextBox1.Value), ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0)
will find the entered value. But it won't find strings.

The challenge is to find code that will work with both or mixed data types.

Thanks for the problem! :think:

[shanu]

It works.Thanks again Mike :super:

[mikerickson]

This will work with both strings and numbers.


Private Sub CommandButton1_Click()
Dim rowNumber As Long

With Me.TextBox1

On Error Resume Next
rowNumber = Application.Match(Val(.Value), ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0)
rowNumber = Application.Match(.Value, ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0)
On Error GoTo 0

If rowNumber = 0 Then
MsgBox .Value & " is not in column A."
Else
MsgBox .Value & " is in row " & CStr(rowNumber) & " of column A."
End If
End With

End Sub

[shanu]

Thats simply Great!!!!!