Hi Mike and other readers,

I can't get this thing to work somehow. I copied in an empty module, but get
several errors. I used to work with these thing 5 years ago, but all the
things I know are gone haha.

Anyway. I have created a simple sheet now, filled column A with 1-4. Went to
create a macro via the VBA-editor. Copied in your text. Is there something I
do wrong from the beginning?

Thnx
Rody

"Mike H" wrote:

Sorry an error, try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
valentered = Target.Value
Target.Value = 0
If valentered < WorksheetFunction.Max(Range("A1:A100")) + 1
Then
Target.Value = WorksheetFunction.Max(Range("A1:A100")) + 1
Else
Target.Value = valentered
End If
Application.EnableEvents = True
End If
End If
End Sub

Mike

"Mike H" wrote:

Hi,

In your post you switch between saying you want columns and rows correcting
so I'm not sure which you want. So a guess. This looks at A1 to A100 and
ensures the numbers are entered sequentially.

Right click the sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
ValEntered = Target.Value
Target.Value = 0
If ValEntered < WorksheetFunction.Max(Range("A1:A100")) + 1
Then
Target.Value = WorksheetFunction.Max(Range("A1:A100")) + 1
End If
Application.EnableEvents = True
End If
End If
End Sub


Mike

"Pair_of_Scissors" wrote:

I (sometimes) have a row with numbers from 1 to 5...

What I want is: when I enter value = 3, and that value (3) already exists in
the column, it has to correct that automatically into 4, with or without a
warning.

Sometimes the row don't have values in it at all, if the value 2 is entered
then, it automatically has to change to 1.

In short: it has to change automatically in the first unused value closest
to 0...

Thanks for the help!!!