VBA code help [small code]

[creativity]

Hi Everyone,

I have a small code which ensures that the same value is not written in the cells.
So I have a row where [in another] worksheet I have a dropdown menu and when the user selects a value from the drop down list, that value is then written in another worksheet. But what I want is that the value does not repeat in the row if it already exists.

My idea is:

Create two counters, i and j. Where i will look at the values from the left of the row and j will look at the row starting from the right of the row. Then if i = j, don't replicate the same value.

My code is:

i = Worksheets("").Cells(1,coli)
j = Worksheets("").Cells(1,coli-1)

While i<>j Then
......

Wend

So what I am not getting is how do I assign j to be a reverse counter?

Thank you

[Kenneth Hobs]

Welcome to the forum! I am a little unclear on what you need. Can you make a short example file and attach it?

[creativity]

Welcome to the forum! I am a little unclear on what you need. Can you make a short example file and attach it?

Hi Kenneth

I apologise I am new to vba so may not be able to explain my concern properly.

So I have a program, which in one worksheet has a drop down menu: in Worksheet 1. That value then gets written to another worksheet in row 5. So what I want is if that value already exists in the row then don't replicate that value.

I do not have an exact code as I am unsure of what to do exactly.

Thanks

[Bob Phillips]

If it is already there, does it matter if it overwrites it? Replacing A with A is the same as replacing B with A.

Or are you asking to write in the next columns as per this event code?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastcol

If Target.Address = "$B$5" Then

With Worksheets("Sheet2")

If IsError(Application.Match(Target.Value, .Rows(5), 0)) Then

If .Range("A5").Value = "" Then

.Range("A5").Value = Target.Value
Else

lastcol = .Cells(5, .Columns.Count).End(xlToLeft).Column
.Cells(5, lastcol + 1).Value = Target.Value
End If
End If
End With
End If
End Sub

[creativity]

Hi,

Yes it wouldn't matter if the value is overwritten. I just don't want the value to be repeated.

Also there are a number of values that have to be checked will this code will with multiple values?

Thanks

[Bob Phillips]

Yes, just check them all with an or statement.