searching a column for duplicate, then replacing the row.

G

GregJG

i have a userform that pulls in information from a row on
spreadsheet.

i.e.

workbook(1).sheets(1) column A contains a list of numbers.
userform1.listbox1 has a list of all the numbers in column A
when the listbox1 is clicked it fills in all the appropriate control
with what is in the other columns on the same row. column A number i
placed in textbox1 on userform.

right now, after changes to the form and the user clicks th
commandbutton to save. it creates a whole new row with the same numbe
in column A, so i have duplicates.

is there a way to replace the entire row instead? with a msgbox askin
if they want the row replaced
 
G

GregJG

to be more specific. this is the code I am using now.

Set SourceWb = Workbooks.Open("f:\bidditjobs.xls")
ActiveWorkbook.Sheets("Jobs").Activate
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtJobNumber.Value
ActiveCell.Offset(0, 1) = cobRep.Value
ActiveCell.Offset(0, 2) = txtMainPrice.Value
ActiveCell.Offset(0, 3) = txtMainPercent.Value
ActiveCell.Offset(0, 4) = txtOptPrice.Value
ActiveCell.Offset(0, 5) = txtOptPercent.Value
ActiveCell.Offset(0, 6) = cobCustName.Value
ActiveCell.Offset(0, 7) = cobSubd.Value
' there is a total of 90

SourceWb.Close True

what i am trying to do is search through column A for th
txtjobnumber.value , if it already exists, then I want to replace tha
row with all new values
 
D

Dave Peterson

I didn't take the time to set up a userform, so this is untested!

option explicit
Sub testme()

Dim res As Variant
Dim SourceWB As Workbook
Dim SourceRng As Range
Dim DestCell As Range

If txtJobNumber.Value = "" Then
MsgBox "Please enter a value!"
Exit Sub
End If

Set SourceWB = Workbooks.Open("f:\bidditjobs.xls")
With SourceWB.Sheets("Jobs")
Set SourceRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
res = Application.Match(txtJobNumber.Value, SourceRng, 0)
If IsError(res) Then
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
Else
Set DestCell = SourceRng(res)
End If
With DestCell
.Offset(0, 0).Value = txtJobNumber.Value
.Offset(0, 1).Value = cobRep.Value
.Offset(0, 2).Value = txtMainPrice.Value
.Offset(0, 3).Value = txtMainPercent.Value
.Offset(0, 4).Value = txtOptPrice.Value
.Offset(0, 5).Value = txtOptPercent.Value
.Offset(0, 6).Value = cobCustName.Value
.Offset(0, 7).Value = cobSubd.Value
' there is a total of 90
End With
End With

SourceWB.Close savechanges:=True
End Sub
 
G

GregJG

thanks for the reply dave.

is still creating duplicate numbers in column A though. I will kee
plugging away at it thoug
 
D

Dave Peterson

Without having the data...

Try checking to see if they're really duplicates. If you have two that look the
same (say in A10 and a99)

put this in an empty cell:
=a10=a99
Do you get TRUE or FALSE?

If false, maybe you have leading/trailing/embedded blanks that are messing
things up.
 
G

GregJG

not sure how anything could be leading, trailing or embedded.

when the form is opened. it produces a jobnumber (date and row number
automatically with this code in the userform_initialize. the tabsto
for txtjobnumber is false so it cannot be altered.

Set wb = Workbooks.Open("f:\BidditJobs.xls")
With wb.Sheets("Jobs")
FName = Range("A65536").End(xlUp).Offset(1, 0).Row
End With
Me.txtJobNumber.Text = Format$(Date, "mmddyy") & FName

I also have a combobox that is created from all the jobnumbers liste
so I can open a previous created job.

With wb.Sheets("jobs")
Set rng = .Range(.Range("A2"), .Range("A2").End(xlDown))
End With
Me.cobOpenPro.List = rng.Value
wb.Close False

I have a Private Sub cobOpenPro_Click() so it will fill in the for
with the information from the previous job. The code is way too big t
post cause there are 87 different control boxes that get filled.

if i open a previous job, make a change to a single checkbox. (not th
jobnumber). and click the save button;

Private Sub cmdSave_Click()

Dim res As Variant
Dim SourceWB As Workbook
Dim SourceRng As Range
Dim DestCell As Range

Set SourceWB = Workbooks.Open("f:\bidditjobs.xls")
With SourceWB.Sheets("Jobs")
Set SourceRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
res = Application.Match(txtJobNumber.Value, SourceRng, 0)
If IsError(res) Then
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
Else
Set DestCell = SourceRng(res)
End If
With DestCell
.Offset(0, 0) = txtJobNumber.Value
.Offset(0, 1) = cobRep.Value
.Offset(0, 2) = txtMainPrice.Value
.Offset(0, 3) = txtMainPercent.Value
.Offset(0, 4) = txtOptPercent.Value
~total of 87 offsets

SourceWB.Close savechanges:=True
End Sub


it is saving the job on a new row instead of replacing the row it i
already on. even though the job number is not being changed.
short example;

I open 08120416 (date:081204 row:16) from my combobox of jobnumbers.
I make a change to the job such as changing the txtmainprice.value
When I click my save button it adds the job to a new row so I end u
with two 08120416 jobs
 
G

GregJG

I have tried to play with the code ya gave me earlier, but to no avail
perhaps if I under stood what the sourcerng meant.

Set SourceRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

the .range("a2",.cells(.row.count,"a") is where I am confused.

that range is for column A only
 
D

Dave Peterson

Is the value in the worksheet a real date with a custom format of mmddyy?

If yes, then the .match won't find it unless you tell it to look for a date.

res = Application.Match(txtJobNumber.Value, SourceRng, 0)

if you look for the value that corresponds to the real date, it may work.

So convert txtjobnumber.value to a date
(with no validation):

dim myDate as date
myDate = dateserial("20"&mid(txtjobnumber.value,5,2), _
mid(txtjobnumber.value,1,2), _
mid(txtjobnumber.value,3,2))

Then later...

res = Application.Match(clng(mydate), SourceRng, 0)

If the value in the worksheet isn't really a date, then this isn't the problem.

(I also assumed all your dates were 2000+.)
 
G

GregJG

I am guessing jobnumber is a real date, or atleast part of it;

With wb.Sheets("Jobs")
FName = Range("A65536").End(xlUp).Offset(1, 0).Row
End With
Me.txtJobNumber.Text = Format$(Date, "mmddyy") & FName

jobnumber is created by current date and next blank row.
at no time do i change the format of jobnumber from above.

I'll try your suggestion, but when the jobnumbers listed in column A o
worksheets"jobs" are set as general and they don't show mmddyy (081404
but show (81404
 
D

Dave Peterson

I thought that your key values were in column A. Is that not true?

It says to start in A2 (headers in A1??)
then go to A65536 and essentially hit end, uparrow
(the bottom most used cell in column A)

If your key data to match isn't in column A, then you'll have to change this.
 
D

Dave Peterson

The match statement looks for an exact match.

So a couple of followup questions.

#1. Is the data in all the cells numeric or text.
=isnumber(a2) would help determine that.

#2. Do you really type in the characters that appear in that column--an exact
match???

If yes, remember that the textbox returns text characters.

maybe clng(txtjobnumber.value) would work if the values in the cells are really
numbers.
 
G

GregJG

I changed

Me.txtJobNumber.Text = Format$(Date, "mmddyy") & FName

to

Me.txtJobNumber.Text = "04" & FName

and tried it. it worked so it does have something to do with the dat
format.

I've tried changing jobnumber to date as you suggested, but no go
 
G

GregJG

I got it !

I added a line to the userform_initialize;

With wb.Sheets("Jobs")
FName = Range("A65536").End(xlUp).Offset(1, 0).Row
End With
Me.txtJobNumber.Text = Format$(Date, "mmddyy") & FName
Me.txtJobNumber.Text = CLng(Me.txtJobNumber.Text)

the last line changed the date format.

couldn't have done it without your help. Thanks!!!

seems I am thanking you alot :-
 
Top