Finding a value

**youngstorm** · 11-18-2010, 05:19 PM

Hi,
I am new to VBA programming. I have a form with 2 textboxs, TextBox1 and TextBox2. I want to type, in TextBox1, in a name that may or may not be in the 'name' column in a spreedsheet. Then have my code go into the sheet and look for that name in the 'name' column. If a match is NOT found, print in TextBox2 "Not Found.". If a match is found, I want the value of another cell from the 'date' column, to be printed to TextBox2. The date cell should be from the same row as the name that matched the search, of course.
I have been studing the find function but it does not make much sence to me. Also, how to go about stringing together objects to get what I want. For example, application.worksheet.cell.value and the like does not seem to do what I would think it should.

Thank you for any help,
youngstorm

**Paul** · 11-18-2010, 05:34 PM

Hi Youngstorm, welcome to the forum.

Take a look at the attached workbook. It has a user form with two textboxes, run by the following code:

Private Sub TextBox1_Change()
Dim found As String
On Error GoTo notFound
found = Sheets("Sheet1").Range("A:A").Find(TextBox1.Text).Offset(0, 2).Value
TextBox2.Text = found
On Error GoTo 0
Exit Sub

notFound:
TextBox2.Text = "NOT FOUND"
End Sub

It automatically updates the date textbox as you start typing the name. Hope that gives you an idea of how to go forward.

**youngstorm** · 11-19-2010, 11:20 AM

Thank you very much. A couple of questions about the code.
1. What is the difference between TextBox1.Text and TextBox1.Value?
2. Can you explain ".Offset(0, 2).Value"?

Youngstorm

**Paul** · 11-19-2010, 11:43 AM

This is an older post, but may help explain the difference between .Text and .Value:
http://www.xtremevbtalk.com/showthread.php?t=108079

I edited the line of code in your question to provide another example of how Range.Find can work. In this example, I used ".Row" at the end. The variable found will contain the Row number where the value was found in this instance.

found = Sheets("Sheet1").Range("A:A").Find(TextBox1.Text).Row

By using ".Offset(0, 2).Value", the Find method is searching for the TextBox1 value in column A, and where found it will look two columns to the right and zero rows down - then return THAT value to the variable.

Offset(R, C) in which a positive row value means "down" and a positive column value means to the right. (Negative mean up and to the left, respectively.)

So ".Offset(-2, 6)" tells it to look 2 rows up and 6 columns to the right of the active/found/referenced cell/range.

**youngstorm** · 11-22-2010, 02:07 PM

OK! I think im starting to get how this works now. Which brings up 2 more questions.

1. Instead of using an offset number, is it possable to use the column name? Ex. Offset(0, Dates)
Yes, I tryes and it does not work like that but maybe you know another way.

2. How do I construct the code to return more than 1 cell per row?

**Paul** · 11-22-2010, 02:34 PM

1. Instead of using an offset number, is it possable to use the column name? Ex. Offset(0, Dates)
Yes, I tryes and it does not work like that but maybe you know another way.

You couldn't use it in the way you tried, no. But you could set the variable found equal to the Row number like I did above, and then use it in a cell reference, e.g.

Cells(found, Range("Dates").Column).Value

If variable "found" is 8, and Range("Dates").Column is 7 (column G), then it will return the value from cell G8.

2. How do I construct the code to return more than 1 cell per row?

You would need to loop through your data to find/return multiple instances.

**youngstorm** · 11-29-2010, 03:04 PM

Originally Posted by Paul

You couldn't use it in the way you tried, no. But you could set the variable found equal to the Row number like I did above, and then use it in a cell reference, e.g.

Cells(found, Range("Dates").Column).Value

If variable "found" is 8, and Range("Dates").Column is 7 (column G), then it will return the value from cell G8.

You would need to loop through your data to find/return multiple instances.

Thanks Paul. It seems to work now but only 1. When I do the following it shows the last item only.

found = Sheets("Solaris").Range("A:A").Find(TextBox1.Text).Offset(0, 0).Value
TextBox2 = "Name: " & found & vbCr

found = Sheets("Solaris").Range("A:A").Find(TextBox1.Text).Offset(0, 7).Value
TextBox2 = "MAC: " & found & vbCr

found = Sheets("Solaris").Range("A:A").Find(TextBox1.Text).Offset(0, 8).Value
TextBox2 = "SN #: " & found & vbCr

What can I do to get it to print all 3 things. There are other things but this 3 are a good start.

Mike