Effective Search Function

[Semantics]

I have this code I've written out in VBA to search and basically I want it to search in column A for a textbox value and, if found, provide the information from columns b-j in the same row so if information is found in a17 I want it to tell me b17, c17, d17 etc all the way to j17 (for example).

Right now it recognizes that a17 has a matching value to what's typed in the textbox, but will only yield results from b2, c2, d2 etc and I know it's got something to do with how I set my range and on next but it's been 2 years and I only remember so much. I need help figuring out what I did wrong and how to fix it.

Here is the code so far:

<tbody>
</tbody>

Private Sub userform1_Initialize()

With Worksheets("Patio 1 Cards" And "Patio 2 Cards")
Set Rng = .Range(.Range("A2:A1000"), .Range("j2:j1000").End(xlDown))
End With
Set Rng = Rng.Resize(, 9)
End Sub

Private Sub TextBox1_AfterUpdate()
On Error Resume Next
TextBox1.Value = Application.WorksheetFunction.VLookup(Me.TextBox1, Worksheets("Patio 1 Cards" And "Patio 2 Cards").Range("A2:A1000"), 2, False)
Me.TextBox1.Value = Application.WorksheetFunction.VLookup(Me.TextBox1, Worksheets("Patio 1 Cards").Range("A:J"), 2, False)
End Sub

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Patio 1 Cards").Visible = True
Sheets("Patio 1 Cards").Select
Range("a2:J2").Select
For A = 1 To 1500
If ActiveCell.Value = TextBox1.Value = False Then
ActiveCell.Offset(1, 0).Select
End If
Next A
ActiveCell.Offset(1, 0).Copy

Sheets("Patio 1 Cards").Select
ActiveCell.Offset(0, 2).Copy

Sheets("Patio 1 Cards").Visible = True
MsgBox "Purchaser last name: " & Sheets("Patio 1 Cards").Range("b2").Value & " Occupant first name: " & Sheets("Patio 1 Cards").Range("C2").Value & " Occupant last name: " & _
Sheets("Patio 1 Cards").Range("d2").Value & " Complex: " & Sheets("Patio 1 Cards").Range("e2").Value & " Patio: " & Sheets("Patio 1 Cards").Range("f2").Value & " Tier: " & _
Sheets("Patio 1 Cards").Range("g2").Value & " Crypt Number: " & Sheets("Patio 1 Cards").Range("h2").Value & " Contract Number: " & Sheets("Patio 1 Cards").Range("i2").Value & _
" GPID: " & Sheets("Patio 1 Cards").Range("j2").Value & "." & _
vbNewLine & vbNewLine & "If this is not the information you are looking for, please try a new search." & vbNewLine & "Check for any spelling errors.", vbInformation + vbOKOnly, "Property Inquiry"
Unload Me

End Sub

I know it's not perfect, but if anyone can at least sort out where the problem is that would help, too. Thanks in advance!

 

[Fluff]

Hi & welcome to the board.
If you're still looking for a solution to this, how about

Private Sub CommandButton1_Click()
   
   Dim Fnd As Range
   Dim Ary As Variant
   
   With Sheets("Patio 1 Cards")
      Set Fnd = .Range("A:A").find("AL7 1UU", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         MsgBox "Not Found"
         Exit Sub
      End If
   End With
   Ary = Fnd.Resize(, 10)
   MsgBox "Purchaser last name: " & Ary(1, 2) & " Occupant first name: " & Ary(1, 3) & " Occupant last name: " & _
   Ary(1, 4) & " Complex: " & Ary(1, 5) & " Patio: " & Ary(1, 6) & " Tier: " & _
   Ary(1, 7) & " Crypt Number: " & Ary(1, 8) & " Contract Number: " & Ary(1, 9) & _
   " GPID: " & Ary(1, 10) & "." & _
   vbNewLine & vbNewLine & "If this is not the information you are looking for, please try a new search." & vbNewLine & "Check for any spelling errors.", vbInformation + vbOKOnly, "Property Inquiry"
   Unload Me

End Sub