PDA

View Full Version : Matching First Name & Name Text Boxes on Form with Corresponding Columns TROUBLESHOOT



bensdb
02-05-2014, 06:42 AM
Hi- I've been desperately trying to do this for hours to no avail. Im learning VBA and have bodged together the following, but I know the for loop that I have got in there cant be right.

Ive got a worksheet with First Name in Column B and Last Name in Column C.

From a userform with two Textboxes, one for first name, and the second for the last name, I need to run a search to find out if there is a match.

For some weird reason this code works about 60/70% of the time in finding matches, but then quite often it comes out as no match when there is!!

I know my code is a bit of a mess and the loop is almost certainly wrong.

PLEASE Help ME!!!!!!!


Sub findmatches()
Set sdsheet = ThisWorkbook.Sheets("Kids")
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
newrng = sdsheet.Range("B1:B" & sdLR)
stringya = findfrm.blah1.Text
stringyb = findfrm.blah2.Text
stringy1 = "*" & stringya & "*"
stringy2 = "*" & stringyb & "*"
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
sdLR = 2
Else
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If
If IsError(Application.Match(stringy1, newrng, 0)) Then
Else
For X = 2 To sdLR
myvalue1 = Application.Match(stringy1, newrng, 0)
myvalue2 = myvalue1 + 1
newrng2 = sdsheet.Range("C" & myvalue1 & ":C" & myvalue2)
Next X
If IsError(Application.Match(stringy2, newrng2, 0)) Then
MsgBox "no match"
Else
MsgBox "match baby"
End If
End If
End Sub