PDA

View Full Version : Delete a row with the oldest date when project numbers match



BizzWhiz
11-11-2007, 01:06 PM
New to VBA and I am using UserForms to add rows to a spread sheet with 7 Columns. Column A has the Project Number and Column G has the date updated. Another worksheet uses a vlookup on the project Number column so I want to delete the duplicates and retain the newest update.:dunno

Thanks for any help

mikerickson
11-11-2007, 01:24 PM
When updating a project, if you overwrite the existing data (rather than writting a new row and deleting the old), the problem of duplicates disappears.

Does that help?

BizzWhiz
11-11-2007, 01:47 PM
The Userform I setup adds a new row to the work sheet how can I use a data form type userform? Where the Project Number when selected would populate the other 7 text boxes and allow me to edit and save in the same row the existing record was located?

mikerickson
11-11-2007, 02:41 PM
Its hard to comment on a spreadsheet/userform that one can't see. If you would post a redacted version of your workbook, it would help.

In general, I would set it up so that a ListBox or ComboBox was populated from the Project Number column. That way the ListSelect property would show which row to write the new data on.

BizzWhiz
11-11-2007, 02:58 PM
This is the code for my userform
patched together from Contextures

Private Sub cmdAdd_Click()
Dim IRow As Long
Dim ws As Worksheet
Set ws = Worksheets("P.M. Update DB")
'find first empty row in database
IRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.ProjectNmbr.Value) = "" Then
Me.ProjectNmbr.SetFocus
MsgBox "Please enter Project Number"
Exit Sub
End If
'copy the data to the database
ws.Cells(IRow, 1).Value = Me.ProjectNmbr.Value
ws.Cells(IRow, 2).Value = Me.TurnOverDate.Value
ws.Cells(IRow, 3).Value = Me.ClientKickOffDate.Value
ws.Cells(IRow, 4).Value = Me.ProductionTurnOverDate.Value
ws.Cells(IRow, 5).Value = Me.UpdatedErectionStartDate.Value
ws.Cells(IRow, 6).Value = Me.User.Value
ws.Cells(IRow, 7).Value = Me.Todaysdate.Value
'clear the data
Me.ProjectNmbr.Value = ""
Me.TurnOverDate.Value = ""
Me.ClientKickOffDate.Value = ""
Me.ProductionTurnOverDate.Value = ""
Me.UpdatedErectionStartDate.Value = ""
Me.User.Value = ""
Me.Todaysdate.Value = ""
Me.ProjectNmbr.SetFocus
End Sub

mikerickson
11-11-2007, 04:11 PM
Please wrap your code. (Select the code and click the # symbol above the editing window.)

I changed the line defining IRow. It should now overwrite the existing data.
Private Sub cmdAdd_Click()
Dim IRow As Long
Dim ws As Worksheet
Set ws = Worksheets("P.M. Update DB")
'find first empty row in database

Rem changed line ********
IRow = ws.Cells(Application.Match(Me.ProjectNmbr.Value, ws.Parent.Range("a:a"), 0),1)

'check for a part number
If Trim(Me.ProjectNmbr.Value) = "" Then
Me.ProjectNmbr.SetFocus
MsgBox "Please enter Project Number"
Exit Sub
End If
'copy the data to the database
ws.Cells(IRow, 1).Value = Me.ProjectNmbr.Value
ws.Cells(IRow, 2).Value = Me.TurnOverDate.Value
ws.Cells(IRow, 3).Value = Me.ClientKickOffDate.Value
ws.Cells(IRow, 4).Value = Me.ProductionTurnOverDate.Value
ws.Cells(IRow, 5).Value = Me.UpdatedErectionStartDate.Value
ws.Cells(IRow, 6).Value = Me.User.Value
ws.Cells(IRow, 7).Value = Me.Todaysdate.Value
'clear the data
Me.ProjectNmbr.Value = ""
Me.TurnOverDate.Value = ""
Me.ClientKickOffDate.Value = ""
Me.ProductionTurnOverDate.Value = ""
Me.UpdatedErectionStartDate.Value = ""
Me.User.Value = ""
Me.Todaysdate.Value = ""
Me.ProjectNmbr.SetFocus
End Sub

Bob Phillips
11-11-2007, 04:22 PM
... If you would post a redacted version of your workbook, it would help.


Nice word! I anticipate (over)using it as much as possible from now on.

BizzWhiz
11-11-2007, 04:36 PM
Getting arun time 438 Error

IRow = ws.Cells(Application.Match(Me.ProjectNmbr.Value, ws.Parent.Range("a:a"), 0),1)

mikerickson
11-11-2007, 04:41 PM
I had to guess about some things.
1) Is ProjectNmber a text box holding the project number?
2) Is the project number in that box on the sheet?

BizzWhiz
11-11-2007, 04:45 PM
1 yes
2 yes

BizzWhiz
11-11-2007, 04:48 PM
1

mikerickson
11-11-2007, 05:32 PM
If Worksheets("P.M. Update DB") column A contains the list of project numbers, I don't understand why its erroring.

BizzWhiz
11-11-2007, 05:56 PM
What can I do