Question on 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 = rngFind.Offset(0, 1).Value
Me.cmbCon = rngFind.Offset(0, -1).Value

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

Regards,
Jade

Question on Find method in vba
 

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 a on 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 = rngFind.Offset(0, 1).Value
Me.cmbCon = rngFind.Offset(0, -1).Value

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

Regards,
Jade

Question on Find method in vba
 

Hi Steve,

what i mean is I'm using the same userform to recall the original data;
however, if the user changes any values in the textboxes that it will
retain that value vs. what was previously stored so i believe it would
have to do some type of check. Then when I update I would like for the
info on that row to just be updated not a new record created.

hope that helps.

Jade


stevebriz wrote:
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 a on 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 = rngFind.Offset(0, 1).Value
Me.cmbCon = rngFind.Offset(0, -1).Value

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

Regards,
Jade

Question on Find method in vba
 

Ok..I think I got it now.
You basically what confirmation. when they it the cmd button eg:..do
you really want to change this XXXXX
YES/NO
on yes update
On no don;t
correct?

Jade wrote:
Hi Steve,

what i mean is I'm using the same userform to recall the original data;
however, if the user changes any values in the textboxes that it will
retain that value vs. what was previously stored so i believe it would
have to do some type of check. Then when I update I would like for the
info on that row to just be updated not a new record created.

hope that helps.

Jade


stevebriz wrote:
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 a on 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 = rngFind.Offset(0, 1).Value
Me.cmbCon = rngFind.Offset(0, -1).Value

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

Regards,
Jade

Question on Find method in vba
 

To write your data back out (or add a new row as appropriate)

Private Sub CmbWriteData_Click()
Dim rngFind As Range, rng as Range
Set rngFind =Worksheets("Data").Range("D1:d500") _
.Find(Me.txtBox.Value, Lookat:=xlWhole)

If rngFind Is Nothing Then
set rng = Worksheets("Data").Cells(rows.count,4).End(xlup)(2 )
Else
set rng = rngFind
end if
rng.Value = Me.txtBox.Value
rng.Offset(0, 1).Value = Me.txtDes.Value
rng.Offset(0, -1).Value = Me.cmbCon.Value
End sub


--
Regards,
Tom Ogilvy



"Jade" wrote in message
ups.com...
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 = rngFind.Offset(0, 1).Value
Me.cmbCon = rngFind.Offset(0, -1).Value

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

Regards,
Jade