Help to alter code slightly

[novicevbaer]

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

 

[Norman Jones]

Hi NoviceBaer,

Try inserting the line:

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

immediately before the first End If line.

 

[novicevbaer]

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 !

 

[anilsolipuram]

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

 

[novicevbaer]

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.