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

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

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

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(xlCellTypeConstants)
.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
> > >
> > >

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(xlCellTypeConstants)
> .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
> > > >
> > > >

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(xlCellTypeConstants)
.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
> >
> >

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(xlCellTypeConstants)
> .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
> > >
> > >