+ Reply to Thread
Results 1 to 7 of 7

Finding a value

  1. #1
    Registered User
    Join Date
    11-18-2010
    Location
    Valdosta, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Finding a value

    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

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Finding a value

    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:
    Please Login or Register  to view this content.
    It automatically updates the date textbox as you start typing the name. Hope that gives you an idea of how to go forward.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-18-2010
    Location
    Valdosta, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Finding a value

    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

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Finding a value

    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.
    Please Login or Register  to view this content.
    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.

  5. #5
    Registered User
    Join Date
    11-18-2010
    Location
    Valdosta, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Finding a value

    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?

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Finding a value

    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.
    Please Login or Register  to view this content.
    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.

  7. #7
    Registered User
    Join Date
    11-18-2010
    Location
    Valdosta, GA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Finding a value

    Quote Originally Posted by Paul View Post
    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.
    Please Login or Register  to view this content.
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1