[RESOLVED] Excel VBA - Find Rows

[mateo107]

Hello...

I'm trying to write a VBA script that will try to find a string 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:
Dim tempSMTP As String
tempSMTP = txtSMTP.Text

'test code
'MsgBox "Searching for... " & tempSMTP & ".", vbOKOnly
With Worksheets("Sheet1").Range("B:B").Find(tempSMTP, LookIn:=xlValues)

End With

[Spajeoly]

Well, it's a bit different than what you may be used to, but this is how I do it.

For i = 1 to 65536 'Max number of rows possible
If InStr(1, Range("$B$" & i).Text, "The String you want to find") Then
'Do what you would do if it exists here
Else
'Do what you want to do here if it Doesn't Exist
End if
If Range("$B$" & i + 1).Text = "" then Exit For 'Next cell is empty, let's give up!
Next

[si_the_geek]

Here's an alternative, which should be a bit more efficient:
Dim lngRow as Long
Dim booFound as Boolean

booFound = False
With Worksheets("Sheet1")
For lngRow = 1 to .UsedRange.Rows.Count '(detect last use row, only loop up to there)
If InStr(1, .Cells(lngRow,2).Text, tempSMTP) Then
booFound = True
'Do what you would do if it exists here
End if
Next lngRow

If Not(booFound) Then
'Do what you want to do here if it Doesn't Exist
End If
End With

[Spajeoly]

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

So THERE!
;)

[si_the_geek]

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.

[mateo107]

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... :)

[Spajeoly]

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....

[si_the_geek]

Very true. :)

Anyhoo, this is probably what you would want for the "Smith" example, it should cover SMTP too:
If InStr(1, .Cells(lngRow,2).Text, tempSMTP) Then
.Rows(lngRow).Select
.Activate
If MsgBox ("Is this the one you were looking for?", vbYesNo) = vbYes Then
booFound = True
Exit For 'As we found it, exit the loop (and run no more code)
End If
End ifYour 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".