Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
Good morning,
I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
s = "problem Names: " & vbNewLine & For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then s = s & nName.Name & _ "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) _ & "). & vbNewLine End If Next nName if len(s) 20 msgbox s & vbNewline & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical End if -- Regards, Tom Ogilvy "AccessHelp" wrote: Good morning, I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
Hi Tom,
Thanks for the code. Your code works well when there are problem defined names. If there are no problem defined names, it does not work. It keeps showing the message, and it does not proceed to the code "GoTo NoErrors". Below is the code. I took you code and made some cosmetic changes to it. May be, that is why it is not working. Please let me know where I miss. I have a question on the code "If len(p) 20 Then". What does it mean? Thanks. p = "Problem defined names: " & vbNewLine For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 21 Then p = p & "'" & nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & ")" & _ vbNewLine End If Next nName If Len(p) 20 Then MsgBox p & vbNewLine & _ "The defined name must have 21 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If 'If the length of defined name is 21 characters, proceed. GoTo NoErrors "Tom Ogilvy" wrote: s = "problem Names: " & vbNewLine & For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then s = s & nName.Name & _ "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) _ & "). & vbNewLine End If Next nName if len(s) 20 msgbox s & vbNewline & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical End if -- Regards, Tom Ogilvy "AccessHelp" wrote: Good morning, I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
Hi Don,
Thanks for the code. Unfortunately, I don't think it will fit into my code. The code that I posted is a portion of my entire code. Thanks again for the code. "Don Guillett" wrote: try this idea Sub finderrors() For Each nname In ActiveWorkbook.Names If Len(nname.Name) < 8 Then r = 2 Cells(r, 1) = nname.Name r = r + 1 End If Next nname GoTo NoErrors -- Don Guillett Microsoft MVP Excel SalesAid Software "AccessHelp" wrote in message ... Good morning, I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
Well, p is always going to be greater than 20 since the first string you
assign to P is 25 characters long. So just check for longer than 30 characters to be safe: p = "Problem defined names: " & vbNewLine For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 21 Then p = p & "'" & nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & ")" & _ vbNewLine End If Next nName If Len(p) 30 Then MsgBox p & vbNewLine & _ "The defined name must have 21 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If 'If the length of defined name is 21 characters, proceed. GoTo NoErrors -- Regards, Tom Ogilvy "AccessHelp" wrote: Hi Tom, Thanks for the code. Your code works well when there are problem defined names. If there are no problem defined names, it does not work. It keeps showing the message, and it does not proceed to the code "GoTo NoErrors". Below is the code. I took you code and made some cosmetic changes to it. May be, that is why it is not working. Please let me know where I miss. I have a question on the code "If len(p) 20 Then". What does it mean? Thanks. p = "Problem defined names: " & vbNewLine For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 21 Then p = p & "'" & nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & ")" & _ vbNewLine End If Next nName If Len(p) 20 Then MsgBox p & vbNewLine & _ "The defined name must have 21 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If 'If the length of defined name is 21 characters, proceed. GoTo NoErrors "Tom Ogilvy" wrote: s = "problem Names: " & vbNewLine & For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then s = s & nName.Name & _ "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) _ & "). & vbNewLine End If Next nName if len(s) 20 msgbox s & vbNewline & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical End if -- Regards, Tom Ogilvy "AccessHelp" wrote: Good morning, I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Statement
Hi Tom,
I changed it to 30, and it worked perfectly. I am curious how you got 25 characters on p. "p = Problem defined names: " is only 23 characters. Again, thank you very much for your help. "Tom Ogilvy" wrote: Well, p is always going to be greater than 20 since the first string you assign to P is 25 characters long. So just check for longer than 30 characters to be safe: p = "Problem defined names: " & vbNewLine For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 21 Then p = p & "'" & nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & ")" & _ vbNewLine End If Next nName If Len(p) 30 Then MsgBox p & vbNewLine & _ "The defined name must have 21 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If 'If the length of defined name is 21 characters, proceed. GoTo NoErrors -- Regards, Tom Ogilvy "AccessHelp" wrote: Hi Tom, Thanks for the code. Your code works well when there are problem defined names. If there are no problem defined names, it does not work. It keeps showing the message, and it does not proceed to the code "GoTo NoErrors". Below is the code. I took you code and made some cosmetic changes to it. May be, that is why it is not working. Please let me know where I miss. I have a question on the code "If len(p) 20 Then". What does it mean? Thanks. p = "Problem defined names: " & vbNewLine For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 21 Then p = p & "'" & nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & ")" & _ vbNewLine End If Next nName If Len(p) 20 Then MsgBox p & vbNewLine & _ "The defined name must have 21 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If 'If the length of defined name is 21 characters, proceed. GoTo NoErrors "Tom Ogilvy" wrote: s = "problem Names: " & vbNewLine & For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then s = s & nName.Name & _ "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) _ & "). & vbNewLine End If Next nName if len(s) 20 msgbox s & vbNewline & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical End if -- Regards, Tom Ogilvy "AccessHelp" wrote: Good morning, I have the code below, and the code works fine. However, the problem is the code checks problem defined names and messages the user individually, and it would not check the next defined name unless one is corrected. For example, I have two problem defined names: "A000PPLE" and "O000RANGE". The code would alert the user on "A000PPLE" first, and the code would not alert the user on "O000RANGE" unless the defined name "A000PPLE" is corrected. Is there a way to change the code below to alert the user all the problem defined names so that the user can write down all the problem defined names all at once and does not have to wait for another one unless one is fixed? Thanks. For Each nName In ActiveWorkbook.Names If Len(nName.Name) < 8 Then MsgBox "Please check and correct the defined name '" & _ nName.Name & "' (" & Right(nName.RefersTo, Len(nName.RefersTo) - 1) & "). " & _ "The defined name must have 8 characters long and must be corrected " & _ "in order to proceed further.", vbCritical GoTo TheEnd End If Next nName GoTo NoErrors |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
Using an IF statement, or VLOOKUP statement | Excel Programming | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |