VBA - Form Search question

[Seandobson2402]

Hi Guys,

I have created a Form with three Text Boxes - see below;

tPostcode
tStatus
tAvailability

I have three colums on Sheet2 of my workbook detailed Postcode (A), Status (B), Availability (C).

I would like to create a macro (Search button) where I enter a postcode into my tPostcode and it will search through Sheet2 > find the exact value / match and then paste.value the data in the same row but next column (B) into tStatus and the next Column (C) into tAvailability

Say if my search found a postcode for A5, I would like B5 to be copy pasted into tStatus and C5 to be copied into tAvailability

I hope this makes sense.

Also, the form will be accessed through Sheet1 if that makes a difference

Thanks
Sean

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet2").Range("A:A").find(tpostcode, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      tstatus = Fnd.Offset(, 1).Value
      tavailability = Fnd.Offset(, 2).Value
   End If
End Sub

 

[Seandobson2402]

How about

Private Sub CommandButton1_Click()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet2").Range("A:A").find(tpostcode, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      tstatus = Fnd.Offset(, 1).Value
      tavailability = Fnd.Offset(, 2).Value
   End If
End Sub

Thanks Fluff - worked like a charm

I've tried adding code to display a msgbox "No Postcode Found" if there are 0 matching results in Sheet2 Column A however, I seem to have lost it there too. Please see below

Private Sub Search_Click()
   Dim Fnd As Range
   Dim Qty As Long
   Set Fnd = Sheets("FibreAvail").Range("A:A").Find(tPostcode, , , xlWhole, , , False, , False)
Qty = WorksheetFunction.CountIf((Sheet2), Range("A:A"), Srch)
   If Qty = 0 Then
      MsgBox "No postcode found"
      Exit Sub
   End If
   If Not Fnd Is Nothing Then
      tStatus = Fnd.Offset(, 1).Value
      tAvailable = Fnd.Offset(, 2).Value
   End If
End Sub

 

[Fluff]

Try

Private Sub CommandButton1_Click()
   Dim Fnd As Range
   Dim a As String
   
   Set Fnd = Sheets("FibreAvail").Range("A:A").find(tpostcode, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      tstatus = Fnd.Offset(, 1).Value
      tavailability = Fnd.Offset(, 2).Value
   Else
      MsgBox "No postcode found"
   End If
End Sub