| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Characters and change as needed
I have a workbook with 3 columns that have information the user inputs.
Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Sounds like you need data validation:
http://www.contextures.com/xlDataVal01.html http://www.contextures.com/xlDataVal08.html Regards, Ryan--- -- RyGuy "jnf40" wrote: I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Put the following event procedure into the code window for the worksheet you
want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Thanks but I would really like to do it using code
"ryguy7272" wrote: Sounds like you need data validation: http://www.contextures.com/xlDataVal01.html http://www.contextures.com/xlDataVal08.html Regards, Ryan--- -- RyGuy "jnf40" wrote: I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Damn!! That is pretty awesome!!
-- RyGuy "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Thank you for your very kind comment... it is much appreciated.
Rick "ryguy7272" wrote in message ... Damn!! That is pretty awesome!! -- RyGuy "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Well, I guess I wasn't as kind as I should have been. I was VERY impressed
though...could you tell... I knew Excel could handle some data validations tasks, but I thought it was quite feeble compared to what Access is capable of. I now have to reconsider my assumptions of Excel!! I've been a heavy user of Excel for about five years now. Every day I am learning new stuff, and I am constantly amazed at the things this tool is capable of!! I tip my hat to you Rick!! Well done!! -- RyGuy "Rick Rothstein (MVP - VB)" wrote: Thank you for your very kind comment... it is much appreciated. Rick "ryguy7272" wrote in message ... Damn!! That is pretty awesome!! -- RyGuy "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
See inline comments...
Well, I guess I wasn't as kind as I should have been. I was VERY impressed though...could you tell... No, I must have missed that. <g I knew Excel could handle some data validations tasks, but I thought it was quite feeble compared to what Access is capable of. I now have to reconsider my assumptions of Excel!! I've been programming since 1981 (mostly in various forms of Basic, but also Fortran, C, and several UNIX scripting languages) and have found that, with a sufficient amount of effort, there is very little you cannot make code do for you, even in "limited" languages. I've been a heavy user of Excel for about five years now. Me, I have been a light user of Excel for about a year now (I'm retired, so I have no jobs to use it on); however, I have a fairly long track record with VBA's sibling... the compiled version of Visual Basic. I find that I can apply a lot of what I know from compiled VB to the Excel VBA world; the biggest stumbling block for me being a lack of intimate familiarity with Excel's Object Model. Every day I am learning new stuff, Me too! and I am constantly amazed at the things this tool is capable of!! Me too! I tip my hat to you Rick!! Well done!! And, once again, I thank you for your most generous comments. Rick |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Rick thanks for your help, I tweeked it a little and came up with the
following: If Intersect(Range("C9:C28,H9:H28,M9:M28"), Target) Is Nothing Then For Each cell In ActiveSheet.Range("C9:C28,H9:H28,M9:M28") If cell.Value "" Then On Error GoTo Whoops Application.EnableEvents = False c = Val(Left(cell.Value, Len(cell.Value) - 1)) & _ "-" & UCase(Right(cell.Value, 1)) If c Like "####-[A-Z]" Or c Like "#####-[A-Z]" Then cell.Value = c Else MsgBox "Your entry is not correct!", vbExclamation cell.Select End If End If Whoops: Application.EnableEvents = True Next End If again thanks I tried using your code to also find if an entry for a name might have left the space out, for example johnsmith and have it give the same msg but it did not work. Any suggestions? I have the following code which checks for various things but can't seem to get it to check for a left out space or even to add a period after a middle initial. If Intersect(Range("B9:B28,G9:G28,L9:L28"), Target) Is Nothing Then For Each cell In Range("B9:B28,G9:G28,L9:L28") If cell.Value "" Then cell.Formula = StrConv(cell.Formula, vbProperCase) l = Len(cell.Value) For i = 1 To l If Mid(cell.Value, i, 3) = " mc" Or Mid(cell.Value, i, 2) = "Mc" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 4) = " mac" And Mid(cell.Value, i, 5) < " mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "Mac" And Mid(cell.Value, i, 4) < "Mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = " o'" Or Mid(cell.Value, i, 2) = "O'" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 8) = "Van Den " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van den " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 8) = "Van Der " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van der " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 3) = "De " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "de " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "La " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "la " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 4) = "Van " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If Mid(cell.Value, i, 4) = "Von " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "von " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If cell.Value = "pool" Or cell.Value = "Pool" Then cell.Value = "POOL" Next End If Next End If End If again thanks! "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
I have to step out for a little while, so I'll look at your second question
when I get back; however, I think I don't like the change you made to my posted code. Are you still running it in the Change event (hard to tell for sure as you left the event head out of your message)? If so, then your For Each loop is just wasting time. The Change event reacts to the cell that has just been changed. For what you want to do, that cell's content is the only one that the event procedure should be looking at. Theoretically, the other cells you are checking with your For-Each loop were already handled when they were entered... there is no reason to check them again... they won't have changed. IF they did change, then the Change event code I initially posted would handle them then-and-there. You can sort of think of the Change event as a giant For-Each loop if you want... for each cell (within the specified range) that gets changed, the event code is executed against that cell (where the Target argument is Set by the system to refer to the cell being changed). As I said, I'll look at your second question later on today (but it does, at first glance, look like it can be shortened considerably). Rick "jnf40" wrote in message ... Rick thanks for your help, I tweeked it a little and came up with the following: If Intersect(Range("C9:C28,H9:H28,M9:M28"), Target) Is Nothing Then For Each cell In ActiveSheet.Range("C9:C28,H9:H28,M9:M28") If cell.Value "" Then On Error GoTo Whoops Application.EnableEvents = False c = Val(Left(cell.Value, Len(cell.Value) - 1)) & _ "-" & UCase(Right(cell.Value, 1)) If c Like "####-[A-Z]" Or c Like "#####-[A-Z]" Then cell.Value = c Else MsgBox "Your entry is not correct!", vbExclamation cell.Select End If End If Whoops: Application.EnableEvents = True Next End If again thanks I tried using your code to also find if an entry for a name might have left the space out, for example johnsmith and have it give the same msg but it did not work. Any suggestions? I have the following code which checks for various things but can't seem to get it to check for a left out space or even to add a period after a middle initial. If Intersect(Range("B9:B28,G9:G28,L9:L28"), Target) Is Nothing Then For Each cell In Range("B9:B28,G9:G28,L9:L28") If cell.Value "" Then cell.Formula = StrConv(cell.Formula, vbProperCase) l = Len(cell.Value) For i = 1 To l If Mid(cell.Value, i, 3) = " mc" Or Mid(cell.Value, i, 2) = "Mc" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 4) = " mac" And Mid(cell.Value, i, 5) < " mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "Mac" And Mid(cell.Value, i, 4) < "Mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = " o'" Or Mid(cell.Value, i, 2) = "O'" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 8) = "Van Den " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van den " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 8) = "Van Der " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van der " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 3) = "De " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "de " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "La " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "la " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 4) = "Van " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If Mid(cell.Value, i, 4) = "Von " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "von " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If cell.Value = "pool" Or cell.Value = "Pool" Then cell.Value = "POOL" Next End If Next End If End If again thanks! "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check Characters and change as needed
Yes you are right. I had it in the Worksheet_SelectionChange rather than the
Worksheet_Change. I changed it back to your offering and it works fine, but it doesn't check for duplicates, either way. "Rick Rothstein (MVP - VB)" wrote: I have to step out for a little while, so I'll look at your second question when I get back; however, I think I don't like the change you made to my posted code. Are you still running it in the Change event (hard to tell for sure as you left the event head out of your message)? If so, then your For Each loop is just wasting time. The Change event reacts to the cell that has just been changed. For what you want to do, that cell's content is the only one that the event procedure should be looking at. Theoretically, the other cells you are checking with your For-Each loop were already handled when they were entered... there is no reason to check them again... they won't have changed. IF they did change, then the Change event code I initially posted would handle them then-and-there. You can sort of think of the Change event as a giant For-Each loop if you want... for each cell (within the specified range) that gets changed, the event code is executed against that cell (where the Target argument is Set by the system to refer to the cell being changed). As I said, I'll look at your second question later on today (but it does, at first glance, look like it can be shortened considerably). Rick "jnf40" wrote in message ... Rick thanks for your help, I tweeked it a little and came up with the following: If Intersect(Range("C9:C28,H9:H28,M9:M28"), Target) Is Nothing Then For Each cell In ActiveSheet.Range("C9:C28,H9:H28,M9:M28") If cell.Value "" Then On Error GoTo Whoops Application.EnableEvents = False c = Val(Left(cell.Value, Len(cell.Value) - 1)) & _ "-" & UCase(Right(cell.Value, 1)) If c Like "####-[A-Z]" Or c Like "#####-[A-Z]" Then cell.Value = c Else MsgBox "Your entry is not correct!", vbExclamation cell.Select End If End If Whoops: Application.EnableEvents = True Next End If again thanks I tried using your code to also find if an entry for a name might have left the space out, for example johnsmith and have it give the same msg but it did not work. Any suggestions? I have the following code which checks for various things but can't seem to get it to check for a left out space or even to add a period after a middle initial. If Intersect(Range("B9:B28,G9:G28,L9:L28"), Target) Is Nothing Then For Each cell In Range("B9:B28,G9:G28,L9:L28") If cell.Value "" Then cell.Formula = StrConv(cell.Formula, vbProperCase) l = Len(cell.Value) For i = 1 To l If Mid(cell.Value, i, 3) = " mc" Or Mid(cell.Value, i, 2) = "Mc" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 4) = " mac" And Mid(cell.Value, i, 5) < " mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "Mac" And Mid(cell.Value, i, 4) < "Mack" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = " o'" Or Mid(cell.Value, i, 2) = "O'" Then cell.Value = _ Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) & Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) & Mid(cell.Value, i + 3, l) If Mid(cell.Value, i, 8) = "Van Den " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van den " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 8) = "Van Der " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van der " & UCase(Mid(cell.Value, i + 8, 1)) & Mid(cell.Value, i + 9, l) If Mid(cell.Value, i, 3) = "De " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "de " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 3) = "La " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "la " & UCase(Mid(cell.Value, i + 3, 1)) & Mid(cell.Value, i + 4, l) If Mid(cell.Value, i, 4) = "Van " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "van " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If Mid(cell.Value, i, 4) = "Von " Then cell.Value = _ Mid(cell.Value, 1, i - 1) & "von " & UCase(Mid(cell.Value, i + 4, 1)) & Mid(cell.Value, i + 5, l) If cell.Value = "pool" Or cell.Value = "Pool" Then cell.Value = "POOL" Next End If Next End If End If again thanks! "Rick Rothstein (MVP - VB)" wrote: Put the following event procedure into the code window for the worksheet you want this functionality on (right click the worksheet tab, select View Code from the popup menu, copy/paste the code into the code window that appears)... Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As String If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _ "-" & UCase(Right(Target.Value, 1)) If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then Target.Value = Answer Else MsgBox "Your entry is not of the proper shape!", vbExclamation Target.Select End If End If Whoops: Application.EnableEvents = True End Sub You do need to change the column references in the first If-Then statement from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check performed on (at least, that is what I think you asked for... if you are only inputting the Vehicle Number into one column, then replace the 3 column references with just a single reference for the column you are interested in). Rick "jnf40" wrote in message ... I have a workbook with 3 columns that have information the user inputs. Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9 to 23. What I need is when the user enters a Vehicle number, which will be 4 or 5 numbers followed by an alpha character, depending on how they enter the number I always want it to format the same way, and then check for duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G or 3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it would always end up showing as 3442-G. The numbers and alpha characters will always be different and there could be 5 numbers instead of 4 such as 36857-H. Any and all help is greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent help needed? How do I limit the number of characters in a | Excel Discussion (Misc queries) | |||
| Sequential numbers with alpha characters help needed | Excel Worksheet Functions | |||
| Formula Needed to Omit Characters | Excel Worksheet Functions | |||
| FIND and REPLACE characters needed | New Users to Excel | |||
| FIND and REPLACE characters needed | Excel Worksheet Functions | |||
Linear Mode
