Code adjustment for numbering items needed-vba

[kelly mort]

Dim e&
e=0
With Sheet1.[A2:A10]
       Do 
            e=e+1
       Loop Until IsError(Application. Match(e, .Cells,0))
End with

I am using this code , which I got from this same forum here some time ago. It's working great, now I need some adjustment.

I want to look at two columns for the numbering.

So for "test purposes", I am using column A and B.

So when I am sending data to column B for the first time, the number should be 1, but if column A is having say 1, then column B should be 2.

The goal is to make sure there is no duplicate- same numbers in both columns .

All I am looking for is a cooler way to avoid the duplicate.

Because at one point, I will send data to one column and at one point I will send to the other column.

And in those instances, my goal is to make sure I don't repeat numbers. If there is a 2 or any number in column B, that number should not come to column A, during the sending process as said above.

Thanks for reading. Kelly

 

[Akuini]

The goal is to make sure there is no duplicate- same numbers in both columns .

Just to be clear you also don't want duplicate number in each column, right? So you can't insert 2 in col A if 2 already exist in col A.

Let's say only 1 & 2 exist in col A:B, then you try to insert 5, is it acceptable or should it change to 3?

 

[kelly mort]

It should take 3

 

[Akuini]

It should take 3

In that case you can use WorksheetFunction.Max.
I don't know how you insert the value to the cell in col A:B, basically you can use it like this:
Let's say the working range is Range("A1:B10") & you want to insert the number to Range("A3")

Range("A3") = WorksheetFunction.Max(Range("A1:B10")) + 1

 

[kelly mort]

The max function won't be a great idea here. I was using that previously.

Since the request is causing a bit of challenge, if this can be made to add letters I think that will be better and save me the trouble .

If I can add letters A and B to my numbering. So that this code will Begin all its numbers with A. A1,A2,A3 etc.

Dim e&
e=0
With Sheet1.[A2:A10]
       Do 
            e=e+1
       Loop Until IsError(Application.Match(e, .Cells,0))
End with

So that when I can differentiate the numbers in various columns