Replace old data

  • Thread starter Thread starter rixanna
  • Start date Start date
R

rixanna

I'm working on a simple program that enabled the user to Search and
Edit the data. The user has 2 options whether to save the data as a new
record or to replace the existing data.

I managed to get the job done for the user to SAVE AS NEW RECORD by
using this coding :

ActiveCell.Value = txtSpecificationNo.Value
ActiveCell.Offset(0, 1) = txtIssueNo.Value
ActiveCell.Offset(0, 2) = lblPly1.Caption
ActiveCell.Offset(0, 3) = txtQtyPly.Value
ActiveCell.Offset(0, 4) = txtCodePly.Value
ActiveCell.Offset(0, 5) = txtLengthPly.Value
ActiveCell.Offset(0, 6) = txtWidthPly.Value
ActiveCell.Offset(0, 7) = txtBiasVolPly.Value
ActiveCell.Offset(0, 9) = txtBuildingInstructionPly.Value
ActiveCell.Offset(0, 10) = txtRevisionPly.Value

Range("A2").Select
End If

The problem now is that I don't know what should I write to let the
user save, as to replace the existing data.

For the time being, I used this code and it did replace the existing
data but it copies the data for 3 to 5 times. So, there are 5 rows of
the same data in the worksheet.

ActiveCell.Value = txtSpecificationNo.Value
ActiveCell.Offset(0, 1) = txtOne.Value
ActiveCell.Offset(0, 2) = lblPly.Caption
ActiveCell.Offset(0, 3) = txtQty.Value
ActiveCell.Offset(0, 4) = txtCode.Value
ActiveCell.Offset(0, 5) = txtLength.Value
ActiveCell.Offset(0, 6) = txtWidth.Value
ActiveCell.Offset(0, 7) = txtBiasVol.Value
ActiveCell.Offset(0, 8) = txtWeight.Value
ActiveCell.Offset(0, 9) = txtBuildingInstruction.Value
ActiveCell.Offset(0, 10) = txtRevision.Value


For i = 2 To 20
If Worksheets("BOOK TABLE").Cells(i, 1).Value = txtSpecificationNo.Text
Then
Worksheets("BOOK TABLE").Cells(i, 2).Value = txtIssue.Text
Worksheets("BOOK TABLE").Cells(i, 3).Value = lblPly.Caption
Worksheets("BOOK TABLE").Cells(i, 4).Value = txtQty.Text
Worksheets("BOOK TABLE").Cells(i, 5).Value = txtCode.Text
Worksheets("BOOK TABLE").Cells(i, 6).Value = txtLength.Text
Worksheets("BOOK TABLE").Cells(i, 7).Value = txtWidth.Text
Worksheets("BOOK TABLE").Cells(i, 8).Value = txtBiasVol.Text
Worksheets("BOOK TABLE").Cells(i, 9).Value = txtWeight.Text
Worksheets("BOOK TABLE").Cells(i, 10).Value =
txtBuildingInstruction.Text
Worksheets("BOOK TABLE").Cells(i, 11).Value = txtRevision.Text
End If
Next i

Can somebody help me?
Thank you in advanced.
 
What's the name of the worksheet that gets the data?

Is it "book table" for both new and replaced data?

Is there a unique key that you can use to match? That way if the key is found
in the data, you know that they're overwriting existing data. If the key isn't
found in the data, then you know that it's new?

I'm gonna guess that txtSpecificationNo.Value is that unique key.

Something like this would go into the Ok button's _Click event:

Option Explicit
Private Sub CommandButton1_Click()

Dim res As Variant
Dim DestCell As Range
Dim BookWks As Worksheet
Dim KeyColRng As Range

Set BookWks = Worksheets("book table")
With BookWks
Set KeyColRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

'do a bunch of validation, especially checking that the key isn't emtpy

If Trim(txtSpecificationNo.Value) = "" Then
Beep
MsgBox "fill in the spec #"
Exit Sub
End If

res = Application.Match(txtSpecificationNo.Value, KeyColRng, 0)

If IsError(res) Then
'key wasn't found, go to the bottom of the list
With BookWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Else
'key was found, use the row that matches
Set DestCell = KeyColRng.Cells(1).Offset(res - 1, 0)
End If

With DestCell
.Value = txtSpecificationNo.Value
.Offset(0, 1).Value = xxx
.Offset(0, 2).Value = xxx
.Offset(0, 3).Value = xxx
.Offset(0, 4).Value = xxx
End With

'clear out existing values in userform here???
End Sub

I didn't create a userform, so you'll have to continue the code for the xxx
stuff.

And I didn't test it, but it compiled ok (after I commented the xxx lines out).
 
Yes, I'm using the same sheet for both new and replaced data.
I also used txtSpecificationNo and txtIssueNo as unique keys.
Let me try to include your code first, and I'll keep you informed what
happens..
 
yeah..thanks a lot. It really worked out.
But, how can I alter the code if I used 2 items as unique keys?
Can I just simply use AND operator to combine both keys.
(the issueno is ni a2)
 
One way:

Option Explicit
Private Sub CommandButton1_Click()

Dim res As Variant
Dim DestCell As Range
Dim BookWks As Worksheet
Dim KeyCol1Rng As Range
Dim KeyCol2Rng As Range
Dim myFormula As String

Set BookWks = Worksheets("book table")
With BookWks
Set KeyCol1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set KeyCol2Rng = KeyCol1Rng.Offset(0, 1) 'next column over
End With

'do a bunch of validation, especially checking that the key isn't empty

If Trim(txtSpecificationNo.Value) = "" Then
Beep
MsgBox "fill in the spec #"
Exit Sub
End If

myFormula = "Match(1,(" & Chr(34) & txtSpecificationNo.Value & Chr(34) _
& "=" & KeyCol1Rng.Address(external:=True) & ")" _
& "*(" & (Chr(34) & txtIssueNumber.Value & Chr(34) _
& "=" & KeyCol2Rng.Address(external:=True) & "), 0)")


res = Application.Evaluate(myFormula)

If IsError(res) Then
'key wasn't found, go to the bottom of the list
With BookWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Else
'key was found, use the row that matches
Set DestCell = KeyCol1Rng.Cells(1).Offset(res - 1, 0)
End If

With DestCell
.Value = txtSpecificationNo.Value
.Offset(0, 1).Value = xxx
.Offset(0, 2).Value = xxx
.Offset(0, 3).Value = xxx
.Offset(0, 4).Value = xxx
End With

'clear out existing values in userform here???
End Sub

I used column A and B, since I didn't know where your data is:

Set KeyCol1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set KeyCol2Rng = KeyCol1Rng.Offset(0, 1) 'next column over

And both should be text values.
 
ps. You'll want to add a check to make sure both of your keys are entered and
make sure you add both keys to the new row.
 
Dave,
The code you gave me does worked out. But, the problem is that, it
didn't replace the existing data but create a new one.
The old code (only one unique key - SpecNo) does replaced the data, but
what I need to do now is that I want to save the data that corresponds
to both SpecNo and IssueNo.(2 unique keys)
 
If the issue number is numeric (or if the spec number is numeric), it could be
the difference between treating the value as text vs a number.

myFormula = "Match(1,(" & Chr(34) & txtSpecificationNo.Value & Chr(34) _
& "=" & KeyCol1Rng.Address(external:=True) & ")" _
& "*(" & (Chr(34) & txtIssueNumber.Value & Chr(34) _
& "=" & KeyCol2Rng.Address(external:=True) & "), 0)")


Maybe:

myFormula = "Match(1,(" & clng(txtSpecificationNo.Value) _
& "=" & KeyCol1Rng.Address(external:=True) & ")" _
& "*(" & clng(txtIssueNumber.Value) _
& "=" & KeyCol2Rng.Address(external:=True) & "), 0)")


Notice that the chr(34) (and associated ampersands) have been dropped.

And the value from the textboxes have been converted to longs (using clng()).

But these are just guesses. I don't know how you entered those values in the
worksheet.
Dave,
The code you gave me does worked out. But, the problem is that, it
didn't replace the existing data but create a new one.
The old code (only one unique key - SpecNo) does replaced the data, but
what I need to do now is that I want to save the data that corresponds
to both SpecNo and IssueNo.(2 unique keys)
Click to expand...
 
Yeahhh, I have change the data type and it works..but there's something
that mekes me feel so confuse..
The actual code in my program is :

Dim res As Variant
Dim DestCell As Range
Dim BookWks As Worksheet
Dim KeyCol1Rng As Range
Dim KeyCol2Rng As Range
Dim myFormula As String

Set BookWks = Worksheets("PLY TABLE")
With BookWks
Set KeyCol1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set KeyCol2Rng = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
End With

myFormula = "Match(1,(" & Chr(34) & txtSpecificationNo.Value & Chr(34)
_
& "=" & KeyCol1Rng.Address(external:=True) & ")"
_
& "(" & Val(txtIssueNo.Value) _
& "=" & KeyCol2Rng.Address(external:=True) & "),
0)"

res = Application.Evaluate(myFormula)
If IsError(res) Then
'key wasn't found, go to the bottom of the list
With BookWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Else
'key was found, use the row that matches
Set DestCell = KeyCol1Rng.Cells(1).Offset(res - 1, 0)
End If

With DestCell
..Value = txtSpecificationNo.Value
.Offset(0, 1).Value = txtIssueNo.Value
.Offset(0, 2).Value = lblPly.Caption
.Offset(0, 3).Value = txtQtyPly.Value
.Offset(0, 4).Value = txtCodePly.Value
.Offset(0, 5).Value = txtLengthPly.Value
.Offset(0, 6).Value = txtWidthPly.Value
.Offset(0, 7).Value = txtBiasVolPly.Value
.Offset(0, 8).Value = txtWeightPly1.Value
.Offset(0, 9).Value = txtBuildingInstructionPly.Value
.Offset(0, 10).Value = txtRevisionPly.Value
End With

but, why does the data for weight not just overwriting the
corresponding SpecNo and IssueNo but other SpecNo and IssueNo in the
worksheet as well?
 
I don't know. It looks like the code is ok to me. Are you sure your textboxes
are named what you want?
Yeahhh, I have change the data type and it works..but there's something
that mekes me feel so confuse..
The actual code in my program is :

Dim res As Variant
Dim DestCell As Range
Dim BookWks As Worksheet
Dim KeyCol1Rng As Range
Dim KeyCol2Rng As Range
Dim myFormula As String

Set BookWks = Worksheets("PLY TABLE")
With BookWks
Set KeyCol1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set KeyCol2Rng = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
End With

myFormula = "Match(1,(" & Chr(34) & txtSpecificationNo.Value & Chr(34)
_
& "=" & KeyCol1Rng.Address(external:=True) & ")"
_
& "(" & Val(txtIssueNo.Value) _
& "=" & KeyCol2Rng.Address(external:=True) & "),
0)"

res = Application.Evaluate(myFormula)
If IsError(res) Then
'key wasn't found, go to the bottom of the list
With BookWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Else
'key was found, use the row that matches
Set DestCell = KeyCol1Rng.Cells(1).Offset(res - 1, 0)
End If

With DestCell
.Value = txtSpecificationNo.Value
.Offset(0, 1).Value = txtIssueNo.Value
.Offset(0, 2).Value = lblPly.Caption
.Offset(0, 3).Value = txtQtyPly.Value
.Offset(0, 4).Value = txtCodePly.Value
.Offset(0, 5).Value = txtLengthPly.Value
.Offset(0, 6).Value = txtWidthPly.Value
.Offset(0, 7).Value = txtBiasVolPly.Value
.Offset(0, 8).Value = txtWeightPly1.Value
.Offset(0, 9).Value = txtBuildingInstructionPly.Value
.Offset(0, 10).Value = txtRevisionPly.Value
End With

but, why does the data for weight not just overwriting the
corresponding SpecNo and IssueNo but other SpecNo and IssueNo in the
worksheet as well?
Click to expand...
 
Yesss....the textboxes are named exactly like the one in the VB
forms..I just don't know why it happens that way..
 
I don't see anything in the code that would treat that one textbox (weight) any
differently from the other textboxes.

Maybe it's something in the code you haven't shared.
Yesss....the textboxes are named exactly like the one in the VB
forms..I just don't know why it happens that way..
Click to expand...
<<snipped>>
 
Dave,
Thank you soooo much for your help. I finally managed to find the
reason why it happened that way. I should realize from the beginning
that the IssueNo is not in the 5th cell (as in the code you sent me -
it's just the example). Mine is in the 2nd cell.

Thanks anyway for your help. It makes me feel good because I'm not
quite familiar with this language(VBA), but there's someone who could
help me to learn this.

Thank you.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

loop until empty row and column problem (format cells until the lastrow and column in a report) 2
Can Vba workout if a selection is part of a group? 0
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Code takes to long to process Excel 2007 1
Excel Macro to use cells as a autofilter 0
How do I clear a form without a button 4
adding a semicolon 4
Transfer Data from UserForm to Worksheet 8

Back
Top