For Statement

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.