lifeson
02-04-2009, 02:39 AM
Hi folks, great to see the site back up & running :clap:
I have a workbook that tracks weekly sales figures and I want add some check for duplicate entries
i.e.
Salesman 1 enters his figures for week 1
if salesman 1 then trys to enter his figures again for week 1 that is flagged as a duplicate.
A prompt should then ask if he wants to overwrite his figures
This is where I have got to:
If .Cells(rw, "A") <> Empty Then
'get the items to check
sName = .Cells(rw, 1).Value
weekNo = .Cells(rw, 6).Value
'********************************************************
'check for a duplicate record on sheet "AllRecords"
With wsH
Set rFound = wsH.Columns.Find(sName, lookat:=xlWhole)
If rFound Is Nothing Then
Application.StatusBar = sName & " is not in the history OK to add"
newRow = True
Call AddToHistory(rw, newRow, 0, wsS)
Else
fndRow = rFound.Row
'MsgBox sName & " exists on row " & fndRow & ", checking for weekno " & weekNo
If wsS.Cells(fndRow, 6) <> weekNo Then
Application.StatusBar = sName & "Has a record but not at week no " & weekNo
newRow = True
Call AddToHistory(rw, newRow, 0, wsH)
Else
msg = sName & " Already has a record for week number " & weekNo & vbNewLine
msg = msg & "Do you want to overwrite the history?"
ans = MsgBox(msg, vbYesNo, "Duplicate history on row: " & fndRow)
If ans = vbYes Then
newRow = False
Call AddToHistory(rw, newRow, fndRow, wsH)
Else
'No selected
End If 'Yes/No
End If 'weekNo check
End If 'name check
'format and protect sheet...
End With 'wsH
Else
End If
Next rw
which works to an extent but as more records are added it doesnt pick up the duplicate entrys correctly
I have a workbook that tracks weekly sales figures and I want add some check for duplicate entries
i.e.
Salesman 1 enters his figures for week 1
if salesman 1 then trys to enter his figures again for week 1 that is flagged as a duplicate.
A prompt should then ask if he wants to overwrite his figures
This is where I have got to:
If .Cells(rw, "A") <> Empty Then
'get the items to check
sName = .Cells(rw, 1).Value
weekNo = .Cells(rw, 6).Value
'********************************************************
'check for a duplicate record on sheet "AllRecords"
With wsH
Set rFound = wsH.Columns.Find(sName, lookat:=xlWhole)
If rFound Is Nothing Then
Application.StatusBar = sName & " is not in the history OK to add"
newRow = True
Call AddToHistory(rw, newRow, 0, wsS)
Else
fndRow = rFound.Row
'MsgBox sName & " exists on row " & fndRow & ", checking for weekno " & weekNo
If wsS.Cells(fndRow, 6) <> weekNo Then
Application.StatusBar = sName & "Has a record but not at week no " & weekNo
newRow = True
Call AddToHistory(rw, newRow, 0, wsH)
Else
msg = sName & " Already has a record for week number " & weekNo & vbNewLine
msg = msg & "Do you want to overwrite the history?"
ans = MsgBox(msg, vbYesNo, "Duplicate history on row: " & fndRow)
If ans = vbYes Then
newRow = False
Call AddToHistory(rw, newRow, fndRow, wsH)
Else
'No selected
End If 'Yes/No
End If 'weekNo check
End If 'name check
'format and protect sheet...
End With 'wsH
Else
End If
Next rw
which works to an extent but as more records are added it doesnt pick up the duplicate entrys correctly