Do Loop Until

  • Thread starter Thread starter Kevin O'Neill
  • Start date Start date
K

Kevin O'Neill

So I can't figure out what to put to get out of this Do Loop. It will
loop as long as the name inputed in the MsgBox is already a "bookmark"
(name). So I need it to stop looping, once a name is inputed that is
not already a "bookmark" (name).

Sub AddLoadCase()

Do
Dim mynum
mynum = InputBox("ENTER Load Case Name", "Load Case Namer")
If mynum = "" Then
Exit Sub
End If

Dim nm As name
On Error Resume Next
Set nm = ThisWorkbook.Names(mynum)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name '" & mynum & "' already exists. Please Choose
Anther 'name'!"
End If
Loop Until ?????????????

Range("AD2:AK4").Copy
Range("B1000").End(xlUp).Offset(3, 0).PasteSpecial
Range("B1000").End(xlUp).Offset(0, 0).Select
Range("B1000").End(xlUp).Offset(0, 0).Value = mynum

End Sub
 
Loop Until nm Is Nothing

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Not quite working.

Isn't there a way to set bookmarks in your actual code, and then jump
back to them?
 
I just check and exit when I want:

Option Explicit
Sub AddLoadCase()
Dim mynum As String
Dim nm As Name
Do

mynum = InputBox("ENTER Load Case Name", "Load Case Namer")
If mynum = "" Then
Exit Sub
End If

Set nm = Nothing
On Error Resume Next
Set nm = ThisWorkbook.Names(mynum)
On Error GoTo 0
If nm Is Nothing Then
Exit Do
else
MsgBox "Name '" & mynum & _
"' already exists. Please Choose Anther 'name'!"
End If
Loop

Range("AD2:AK4").Copy
Range("B1000").End(xlUp).Offset(3, 0).PasteSpecial
Range("B1000").End(xlUp).Offset(0, 0).Select
Range("B1000").End(xlUp).Offset(0, 0).Value = mynum

End Sub
 
Barring what Dave Peterson has posted, with your cursor where you want it,
F9 will toggle breakpoints in your code. From the Edit menu in your VBE,
you can toggle bookmarks.

HTH
 
That only happens if first an existing name is chosen and then a new name is
subsequently chosen, as the nm variable still has the previous value (I
found this 'feature' very annoying personally). So you need to initialise it
each iteration of the loop

Sub AddLoadCase()
Do
Dim mynum
mynum = InputBox("ENTER Load Case Name", "Load Case Namer")
If mynum = "" Then
Exit Sub
End If

Dim nm As Name
Set nm = Nothing
On Error Resume Next
Set nm = ThisWorkbook.Names(mynum)
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Name '" & mynum & "' already exists. Please Choose
Anther 'name'!"
End If
Loop Until nm Is Nothing

Range("AD2:AK4").Copy
Range("B1000").End(xlUp).Offset(3, 0).PasteSpecial
Range("B1000").End(xlUp).Offset(0, 0).Select
Range("B1000").End(xlUp).Offset(0, 0).Value = mynum

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Gosh forgotten all about that one.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top