enter data from a form to "found" fields

[Travis]

I use the following code to search for a value in a
column and then display the field values in text boxes on
the form. I need a command to save the changes made to
the field values in the text boxes back to those specific
fields. Please help!

Private Sub cmdSearch_Click()
Dim rng As Range
With Worksheets("Sheet1").Columns(1)
Set rng = .Find(what:=frmSearch.TextBox1.Text, _
After:=Range("A65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not rng Is Nothing Then
With frmSearch
.TextBox2.Text = rng.Offset(0, 1).Value
.TextBox3.Text = rng.Offset(0, 2).Value
' and so forth
End With
Else
MsgBox frmSearch.TextBox1.Text & _
" was not found"
End If
End Sub

 

[Tom Ogilvy]

Either store the rng you used or find it again as below (and reverse the
direction)

Private Sub cmdSearch_Click()
Dim rng As Range
With Worksheets("Sheet1").Columns(1)
Set rng = .Find(what:=frmSearch.TextBox1.Text, _
After:=Range("A65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not rng Is Nothing Then
With frmSearch
rng.Offset(0, 1).Value = .TextBox2.Text
rng.Offset(0, 2).Value = .TextBox3.Text
' and so forth
End With
Else
MsgBox frmSearch.TextBox1.Text & _
" was not found"
End If
End Sub

 

[Guest]

TOM,

Thank you 1,000 times over. I don't know how you learn
all of these nuances of excel and VBA, but I truly
appreciate it. Now I can finish my program and get it in
action next monday, thank you again for your input.

Travis