When a user selects from a list box, the product and the cost ar
automatically placed into columns a and b. The following code was s
kindly provided to me to ensure there are no duplicate values in colum
a. How would I alter the code slightly so that if it does not accep
the duplicate value for column a it will not enter the cost into colum
b as well?

Thank you so much for this it is a critical piece of my plan :)




Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Row = 1 And Target.Row <= 100) And Target.Column = 1
Then
If (Range("a" & Target.Row).Value < "") Then
For i = 1 To 100
If (i < Target.Row And Range("a" & Target.Row).Value < "") Then
If (Range("a" & i).Value = Range("a" & Target.Row).Value) Then
MsgBox "entered duplicate value"
Range("a" & Target.Row).Value = ""
End If
End If
Next
End If
End If
End Su

--
novicevbae
-----------------------------------------------------------------------
novicevbaer's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread.php?threadid=31942

Hi NoviceBaer,

Try inserting the line:

Range("B" & Target.Row).Value = ""

immediately before the first End If line.


---
Regards,
Norman



"novicevbaer" wrote in message
...

When a user selects from a list box, the product and the cost are
automatically placed into columns a and b. The following code was so
kindly provided to me to ensure there are no duplicate values in column
a. How would I alter the code slightly so that if it does not accept
the duplicate value for column a it will not enter the cost into column
b as well?

Thank you so much for this it is a critical piece of my plan :)




Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Row = 1 And Target.Row <= 100) And Target.Column = 1)
Then
If (Range("a" & Target.Row).Value < "") Then
For i = 1 To 100
If (i < Target.Row And Range("a" & Target.Row).Value < "") Then
If (Range("a" & i).Value = Range("a" & Target.Row).Value) Then
MsgBox "entered duplicate value"
Range("a" & Target.Row).Value = ""
End If
End If
Next
End If
End If
End Sub


--
novicevbaer
------------------------------------------------------------------------
novicevbaer's Profile:
http://www.excelforum.com/member.php...o&userid=12567
View this thread: http://www.excelforum.com/showthread...hreadid=319425

Norman,

Thank you for responding to my question. I inserted your cod
immediately before the first end if line and it still puts the produc
cost in column b. any other suggestions? Thank you !

--
novicevbae
-----------------------------------------------------------------------
novicevbaer's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread.php?threadid=31942

How are you entering data into column b , is it through formulae or list
or manual entry?


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=319425

It gets sort of convuluded but here goes....

I have a list of products on a worksheet. When the user selects an
item from those products appearing in a user form based on that sheet,
the listindex value calls the product and the associated cost into a
"selection link" of two cells which are named a range. The product and
the cost are then entered automatically into column a and b respectively
based on that range.


--
novicevbaer
------------------------------------------------------------------------
novicevbaer's Profile: http://www.excelforum.com/member.php...o&userid=12567
View this thread: http://www.excelforum.com/showthread...hreadid=319425