Results 1 to 8 of 8

Thread: [RESOLVED] Excel VBA - Find Rows

Hybrid View

  1. #1

    Thread Starter
    Fanatic Member mateo107's Avatar
    Join Date
    Jan 2005
    Posts
    547

    Resolved [RESOLVED] Excel VBA - Find Rows

    Hello...

    I'm trying to write a VBA script that will try to find a string
    VB Code:
    1. strtemp
    in Row "B" of "Sheet1".

    Essentially, I'm trying to do data entry validation for the worksheet, and I need to make sure the record doesn't already exist. Soooo...

    I've created a form with a button to "search". I'd like the search to go through every record in "Sheet1" , Column "B" to see if any of the text matches the string strtemp. If it does, I would like it to "jump" to that row and prompt for a "continue/cancel".

    If the user presses continue, keep searching until the last row is reached.
    If the user presses cancel, bomb out.

    Can someone help me with this? I'm desperate!
    So far, my meager attempt is:
    VB Code:
    1. Dim tempSMTP As String
    2. tempSMTP = txtSMTP.Text
    3.  
    4. 'test code
    5. 'MsgBox "Searching for... " & tempSMTP & ".", vbOKOnly
    6. With Worksheets("Sheet1").Range("B:B").Find(tempSMTP, LookIn:=xlValues)
    7.  
    8. End With


    -Matthew-

  2. #2
    Frenzied Member Spajeoly's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    1,068

    Re: Excel VBA - Find Rows

    Well, it's a bit different than what you may be used to, but this is how I do it.
    VB Code:
    1. For i = 1 to 65536 'Max number of rows possible
    2.    If InStr(1, Range("$B$" & i).Text, "The String you want to find") Then
    3.       'Do what you would do if it exists here
    4.    Else
    5.       'Do what you want to do here if it Doesn't Exist
    6.    End if
    7.          If Range("$B$" & i + 1).Text = "" then Exit For 'Next cell is empty, let's give up!
    8. Next

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Excel VBA - Find Rows

    Here's an alternative, which should be a bit more efficient:
    VB Code:
    1. Dim lngRow as Long
    2. Dim booFound as Boolean
    3.  
    4. booFound = False
    5. With Worksheets("Sheet1")
    6.   For lngRow = 1 to .UsedRange.Rows.Count  '(detect last use row, only loop up to there)
    7.     If InStr(1, .Cells(lngRow,2).Text, tempSMTP) Then
    8.       booFound = True
    9.       'Do what you would do if it exists here
    10.     End if
    11.   Next lngRow
    12.  
    13.   If Not(booFound) Then
    14.     'Do what you want to do here if it Doesn't Exist
    15.   End If
    16. End With

  4. #4
    Frenzied Member Spajeoly's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    1,068

    Re: Excel VBA - Find Rows

    Eh, my code will only loop until the last row that has anything in column b.

    So THERE!

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Excel VBA - Find Rows

    Yep, but my amendments make it more efficient as the number of rows is placed in the For (evaluated only once) rather than for each row performing a combination of: data type conversion ("i + 1" to a string), string concatenation (very slow), range finding (function call presumably), comparison, and an If statement. There is also the efficiency gain from having a Long for a loop variable (as it is 32-bit, like the computer and OS). So there indeed!

    Also, your "doesn't exist" block didn't seem to be quite what mateo107 is looking for, as it was checking per row.

  6. #6

    Thread Starter
    Fanatic Member mateo107's Avatar
    Join Date
    Jan 2005
    Posts
    547

    Re: Excel VBA - Find Rows

    thank you all very much! I can't wait to try out the code.. my only other thought it...

    So, lets just say for the sake of argument, I'm using SI's code...

    When I get to the first "If found = yes"... What would I do to let the user either continue or stop there. By stop there, I mean, I want that row shown, with that cell in particular selected...

    Since I can't seem to get anything spelled out properly, let me try with some examples, in case I've managed to miss my point ( a somewhat frequent occurrence when it's late)...

    Example 1:

    User is searching for "smith"... to which, there are say 16 rows which contain "Smith" (out of, perhaps, 300 rows of data).
    The first smith that we find is for "Smith, Jane"... and we were looking for "Smith, Robert" (I know this may seem odd, but just keep with me...).

    So, I'd like a popup essentially asking:
    Is this what you wanted? Yes/No (if Yes, stop searching and highlight that row/cell: if no, find the next smith)...
    The next smith is "Smith, Rhonda"... same question as above
    The next smith is "Smith, Robert"... user clicks "yes" and we're now out of the loop...

    Example 2:
    would be the same, except we don't find any "Smith, Roberts" in the column... so I'd like a basic popup saying "No Matches Found".... this would go in the "not found/doesn't exist" block... right?

    (So I guess, I've got a few questions here: 1) is my logic right, in terms of what goes into the "found"/"not found" sections? 2) if found, how do i get that cell selected/set as the active cell...

    Thanks very much folks!

    [EDIT]: My example's a LITTLE ambigious, perhaps, because we'd never have data that is that similar... it's going to be a strict YES/NO. (Unlike names, which may duplicate frequently). I'm actually searching for Unique MailID's (SMTP email addresses), so... I'm essentially making a data validation search to ENSURE that the mailID doesn't already exist in the spreadsheet. Ultimately, we would very infrequently 'find one' that is already on the list...
    Last edited by mateo107; Mar 3rd, 2006 at 02:25 AM.


    -Matthew-

  7. #7
    Frenzied Member Spajeoly's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    1,068

    Re: Excel VBA - Find Rows

    Man, saying something is faster in VBA is like saying a Pinto With premium gas will outrun one with Regular unleaded.

    Then again, I guess VBA needs all the speed it can get in the same argument....

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Excel VBA - Find Rows

    Very true.

    Anyhoo, this is probably what you would want for the "Smith" example, it should cover SMTP too:
    VB Code:
    1. If InStr(1, .Cells(lngRow,2).Text, tempSMTP) Then
    2.       .Rows(lngRow).Select
    3.       .Activate
    4.       If MsgBox ("Is this the one you were looking for?", vbYesNo) = vbYes Then
    5.         booFound = True
    6.         Exit For   'As we found it, exit the loop (and run no more code)
    7.       End If
    8.     End if
    Your assumption about where to put the "not found" message is correct; it will not be shown if the answer to the above message is "yes".

Posting Permissions

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



Click Here to Expand Forum to Full Width