Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
Loop Until nm Is Nothing
-- HTH RP (remove nothere from the email address if mailing direct) "Kevin O'Neill" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
Not quite working.
Isn't there a way to set bookmarks in your actual code, and then jump back to them? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
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 Kevin O'Neill wrote: Not quite working. Isn't there a way to set bookmarks in your actual code, and then jump back to them? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
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 -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Kevin O'Neill" wrote in message ups.com... Not quite working. Isn't there a way to set bookmarks in your actual code, and then jump back to them? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
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) "Kevin O'Neill" wrote in message ups.com... Not quite working. Isn't there a way to set bookmarks in your actual code, and then jump back to them? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
Ah Bob thank-you. I was pulling my hair out with that.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Until
Gosh forgotten all about that one.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Kevin O'Neill" wrote in message oups.com... Ah Bob thank-you. I was pulling my hair out with that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |