Replace old data

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.
 
D

Dave Peterson

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).
 
R

rixanna

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..
 
R

rixanna

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)
 
D

Dave Peterson

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.
 
D

Dave Peterson

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.
 
R

rixanna

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)
 
D

Dave Peterson

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...
 
R

rixanna

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?
 
D

Dave Peterson

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...
 
R

rixanna

Yesss....the textboxes are named exactly like the one in the VB
forms..I just don't know why it happens that way..
 
D

Dave Peterson

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>>
 
R

rixanna

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.
 
Top