|
Change value of cell automatically if entered the wrong number
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!!! |
Change value of cell automatically if entered the wrong number
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!!! |
Change value of cell automatically if entered the wrong number
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!!! |
Change value of cell automatically if entered the wrong number
I'm gonna work with it, and will keep you updated.
Thanks 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!!! |
Change value of cell automatically if entered the wrong number
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!!! |
Change value of cell automatically if entered the wrong number
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 On Mon, 5 May 2008 02:15:01 -0700, Pair_of_Scissors wrote: 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!!! |
Change value of cell automatically if entered the wrong number
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? "Gord Dibben" wrote: 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 On Mon, 5 May 2008 02:15:01 -0700, Pair_of_Scissors wrote: 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!!! |
Change value of cell automatically if entered the wrong number
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? "Gord Dibben" wrote: 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 On Mon, 5 May 2008 02:15:01 -0700, Pair_of_Scissors wrote: 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!!! |
| All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com