 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Change 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!!! 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 > I (sometimes) have a row with numbers from 1 to 5... > [quoted text clipped - 9 lines] > > Thanks for the help!!! 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 > Hi, > [quoted text clipped - 35 lines] > > > > Thanks for the help!!! I'm gonna work with it, and will keep you updated. Thanks Rody > Sorry an error, try this instead > [quoted text clipped - 57 lines] > > > > > > Thanks for the help!!! 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 > Sorry an error, try this instead > [quoted text clipped - 57 lines] > > > > > > Thanks for the help!!! 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 >Hi Mike and other readers, > [quoted text clipped - 70 lines] >> > > >> > > Thanks for the help!!! 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? > What you have done wrong is to not read carefully. > [quoted text clipped - 82 lines] > >> > > > >> > > Thanks for the help!!! 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? > > What you have done wrong is to not read carefully. > > [quoted text clipped - 82 lines] > > >> > > > > >> > > Thanks for the help!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.