Question using the excel find method in vba?
 

Hello Everyone,

I'm working on a userform which gathers the user's input and dumps the
data into a spreadsheet. There is a portion in my code which if the
data is already supplied I use the find method to locate and reinsert
the data in the form. However; in testing it , I realize that I have
to modify it in a way that it doesn't add the data pulled as a new
record when updated and checks for any modifications and if any were
made to keep that data vs. the original version.


Dim rngFind As Range
Set rngFind =
Worksheets("Data").Range("D1:d500").Find(Me.txtBox .Value,
Lookat:=xlWhole)


If rFind Is Nothing Then
MsgBox "Data has not been entered.", vbInformation
Else
Me.txtDes = rFind.Offset(0, 1).Value
Me.cmbCon = rFind.Offset(0, -1).Value


If anyone has any suggestions/guidance, I'd appreciate it.


Regards,
Jade

Question using the excel find method in vba?
 

Jade,
A few questions?
How do you mean check for any modifcations??
once you get the value into you form from the data sheet and then
modifiy it through your form on what sheet are you writing the changes
to?
If you write to a separate sheet then you preserve the original
"data".
sheet.

Jade wrote:
Hello Everyone,

I'm working on a userform which gathers the user's input and dumps the
data into a spreadsheet. There is a portion in my code which if the
data is already supplied I use the find method to locate and reinsert
the data in the form. However; in testing it , I realize that I have
to modify it in a way that it doesn't add the data pulled as a new
record when updated and checks for any modifications and if any were
made to keep that data vs. the original version.


Dim rngFind As Range
Set rngFind =
Worksheets("Data").Range("D1:d500").Find(Me.txtBox .Value,
Lookat:=xlWhole)


If rFind Is Nothing Then
MsgBox "Data has not been entered.", vbInformation
Else
Me.txtDes = rFind.Offset(0, 1).Value
Me.cmbCon = rFind.Offset(0, -1).Value


If anyone has any suggestions/guidance, I'd appreciate it.


Regards,
Jade

Question using the excel find method in vba?
 

here is one way to go

Here I have put code to populate
and code to just search for the entries

Sub populate()
' search for existing record and if found updates the values in the
spreadsheet
' If the entry is not found it adds a new record
' Warning this will update all entries in the sheet if optional #1 not
used
' I have used the Userform name of UserForm1
' This code is inserted in a module
Dim Found As Boolean
Found = False
Dim entryrow As Integer
Dim num_entry As Integer
If UserForm1.txtBox.Value = "" Then
MsgBox " You must enter a value in txtbox"
Else
With Worksheets("Data").Range("D1:d500")
Set rngFind = .Find(UserForm1.txtBox.Value, Lookat:=xlWhole)
If Not rngFind Is Nothing Then
firstAddress = rngFind.Address
Do

Found = True
num_entry = num_entry + 1 ' optional #1
If num_entry 1 Then ' optional #1
MsgBox " First entry updated in Row " & entryrow & " but now
aborting futher update due to multiple entries" ' optional #1

Exit Sub
Else ' optional #1
entryrow = rngFind.row
rngFind.Offset(0, 1).Value = UserForm1.txtDes.Value
rngFind.Offset(0, -1).Value = UserForm1.cmbCon.Value
End If ' optional #1

Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <
firstAddress
End If
End With
If Found = False Then ' if the value in txtbox is not found then add
new entry.
MsgBox " Not found"
Range("D65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = UserForm1.txtBox.Value
ActiveCell.Offset(0, 1) = UserForm1.txtDes.Value
ActiveCell.Offset(0, -1).Value = UserForm1.cmbCon.Value

End If
End If
End Sub

Sub search()
' search for existing record and populates form
' this is incase you want to add a SEARCH button that does not update
' I have used the Userform name of UserForm1
' This code is inserted in a module
Dim Found As Boolean
Found = False
Dim num_entry As Integer
If UserForm1.txtBox.Value = "" Then
MsgBox " You must enter a value in txtbox"
Else
With Worksheets("Data").Range("D1:d500")
Set rngFind = .Find(UserForm1.txtBox.Value, Lookat:=xlWhole)
If Not rngFind Is Nothing Then
firstAddress = rngFind.Address
Do
Found = True
MsgBox UserForm1.txtBox.Value & " Found on Row " & rngFind.row
num_entry = num_entry + 1
UserForm1.txtDes.Value = rngFind.Offset(0, 1).Value
UserForm1.cmbCon.Value = rngFind.Offset(0, -1).Value
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <
firstAddress
End If
End With
If num_entry 1 Then MsgBox "NOTE: " & num_entry & " entries in sheet
for " & UserForm1.txtBox.Value
If Found = False Then
MsgBox UserForm1.txtBox.Value & " Was Not found"

End If
End If


End Sub