Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
Asking for help again.
Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
the code check column A for the customer. then check one column over
offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
Thanks Joel,
The code works but I just get a msgbox that only gives me the option to answer ok, how do I change it to yes or no and if yes replace the row on Data Sheet and if no continue with my previous code. Thank you Winnie "joel" wrote: the code check column A for the customer. then check one column over offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
If it helps my My code for copying the data is
Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim newWks As Worksheet Dim NextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range Dim CustList As Range Application.ScreenUpdating = False Sheets("Input").Unprotect Password:="mypsswrd" Sheets("PriceData").Unprotect Password:="mypsswrd" 'cells to copy from Input sheet - some contain formulas myCopy = "D11,D7,D9,D5,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PriceData") With historyWks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If 'End With With historyWks With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End WithWith inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If 'End With With historyWks With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.GoTo .Cells(1) ', Scroll:=True End With On Error GoTo 0 Application.ScreenUpdating = False Sheets("Input").Protect Password:="mypsswrd" Sheets("PriceData").Protect Password:="mypsswrd" End With End Sub Thanks "winnie123" wrote: Thanks Joel, The code works but I just get a msgbox that only gives me the option to answer ok, how do I change it to yes or no and if yes replace the row on Data Sheet and if no continue with my previous code. Thank you Winnie "joel" wrote: the code check column A for the customer. then check one column over offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
Sorry for the many postings.
I have entered Application.Run "findcustomerProduct" Before With historyWks With .Cells(NextRow, "A") ..Value = Now ..NumberFormat = "mm/dd/yyyy hh:mm:ss" End With And when I test the macro by entering same customer and product the msgbox correctly says record already exixts at price *** replace reocrd. As there is only Ok answer, click ok but the record justs gets added. I need help to get the existing record overwritten with the new info. Thanks Winnie "winnie123" wrote: If it helps my My code for copying the data is Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim newWks As Worksheet Dim NextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range Dim CustList As Range Application.ScreenUpdating = False Sheets("Input").Unprotect Password:="mypsswrd" Sheets("PriceData").Unprotect Password:="mypsswrd" 'cells to copy from Input sheet - some contain formulas myCopy = "D11,D7,D9,D5,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PriceData") With historyWks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If 'End With With historyWks With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End WithWith inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If 'End With With historyWks With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.GoTo .Cells(1) ', Scroll:=True End With On Error GoTo 0 Application.ScreenUpdating = False Sheets("Input").Protect Password:="mypsswrd" Sheets("PriceData").Protect Password:="mypsswrd" End With End Sub Thanks "winnie123" wrote: Thanks Joel, The code works but I just get a msgbox that only gives me the option to answer ok, how do I change it to yes or no and if yes replace the row on Data Sheet and if no continue with my previous code. Thank you Winnie "joel" wrote: the code check column A for the customer. then check one column over offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
I merge the two macro into one. I changed NextRow to New. Then depending if
a duplicate is found and the response to the question I set NewRow to equal the duplicate or the next available row. I don't know what modifications you made to my code so I put the same code in below that I did beofe looking for 123 and apple. You need to fix this section to get the correct customer and product. Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim newWks As Worksheet Dim NewRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range Dim CustList As Range Application.ScreenUpdating = False Sheets("Input").Unprotect Password:="mypsswrd" Sheets("PriceData").Unprotect Password:="mypsswrd" 'cells to copy from Input sheet - some contain formulas myCopy = "D11,D7,D9,D5,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PriceData") Application.Run "findcustomerProduct" With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks customer = "123" Product = "Apple" Found = False With .Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With NewRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row If Found = True Then Response = MsgBox("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?", vbYesNo) If Response = vbYes Then NewRow = c.Row End If End If With .Cells(NewRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NewRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NewRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(NewRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NewRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NewRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.GoTo .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With Application.ScreenUpdating = False Sheets("Input").Protect Password:="mypsswrd" Sheets("PriceData").Protect Password:="mypsswrd" End Sub "joel" wrote: the code check column A for the customer. then check one column over offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicate
Thanks Joel,
Thats is it, It does exactly what I want it to do. Its Great. Many Many Thanks Winnie "joel" wrote: I merge the two macro into one. I changed NextRow to New. Then depending if a duplicate is found and the response to the question I set NewRow to equal the duplicate or the next available row. I don't know what modifications you made to my code so I put the same code in below that I did beofe looking for 123 and apple. You need to fix this section to get the correct customer and product. Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim newWks As Worksheet Dim NewRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range Dim CustList As Range Application.ScreenUpdating = False Sheets("Input").Unprotect Password:="mypsswrd" Sheets("PriceData").Unprotect Password:="mypsswrd" 'cells to copy from Input sheet - some contain formulas myCopy = "D11,D7,D9,D5,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PriceData") Application.Run "findcustomerProduct" With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks customer = "123" Product = "Apple" Found = False With .Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With NewRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row If Found = True Then Response = MsgBox("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?", vbYesNo) If Response = vbYes Then NewRow = c.Row End If End If With .Cells(NewRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NewRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NewRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(NewRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(NewRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(NewRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.GoTo .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With Application.ScreenUpdating = False Sheets("Input").Protect Password:="mypsswrd" Sheets("PriceData").Protect Password:="mypsswrd" End Sub "joel" wrote: the code check column A for the customer. then check one column over offset(0,1) for product. Finally it displays value in column E in the message box moving over 4 columns from A offset(0,4). Sub findcustomerProduct() customer = "123" Product = "Apple" Found = False With Worksheets("Data").Columns("A") Set c = .Find(what:=customer, LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do 'check column B for product If c.ofset(0, 1) = Product Then Found = True Exit Do End If Set c = .FindNext(after:=c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With If Found = True Then MsgBox ("customer and product combination already exist." & _ vbCrLf & _ "Price is (use value from the Data sheet column E : " & _ c.Offset(0, 4) & ")." & _ vbCrLf & _ "Do you wish to replace?") End If End Sub "winnie123" wrote: Asking for help again. Not sure how to go about this but I have read different articles in this forum and on the web but still cant get this sussed. I have a sheet "Input" which I want to use for entering prices. it has data is in column D Customer name - selected via validation list Customer No - uses lookup Currency - uses lookup Product - selected via validation list price - input by user I have the code that will copy the cells to another sheet (Data). What I want to do is before that data is copied check to see if the Customer and Product already exists. If it exists need a msgbox to appear "customer and product combination already exist. Price is (use value from the Data sheet column E) Do you wish to replace. If the user says yes then copy over existing record on Data sheet. if the user says no then clear cells and exit sub. I am using excel 2003. Is this possible? Thank you Winnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if Duplicate with Exceptions | Excel Worksheet Functions | |||
Duplicate check | Excel Worksheet Functions | |||
duplicate check | Excel Worksheet Functions | |||
How can I check for duplicate $'s? | Excel Worksheet Functions | |||
Check for duplicate values? | Excel Programming |