Change value of cell automatically if entered the wrong number

[Pair_of_Scissors]

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!!!

 

[Mike H]

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

 

[Mike H]

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

 

[Pair_of_Scissors]

I'm gonna work with it, and will keep you updated.

Thanks Rody

 

[Pair_of_Scissors]

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

 

[Gord Dibben]

What you have done wrong is to not read carefully.

Mike instructed you to right-click on the sheet tab and "View Code"

Copy/paste the event code into that sheet module.

Watch out for wordwrap.

The orphanned "Then" should be at the end of the line above.


Gord Dibben MS Excel MVP

 

[Pair_of_Scissors]

That was the problem thanks! Thought I'd still how these things work, but
even forgot this... thanks anyway...

Am trying to get it work now for the active column at that moment, so when
column D is active, the same routine should be active. I think I have to
replace the a1-a100?

Any suggestions on how to do this?

 

[Pair_of_Scissors]

Bump Anyone? Please? See text below!

That was the problem thanks! Thought I'd still how these things work, but
even forgot this... thanks anyway...

Am trying to get it work now for the active column at that moment, so when
column D is active, the same routine should be active. I think I have to
replace the a1-a100?

Any suggestions on how to do this?