Do Until Inputbox = loop count

[Guest]

Hi all,

I cannot work out why this isn't working. The user inputs a number from 1
to 4 into an inputbox, and I want the macro to loop for this number of times
(if 1 is entered, an if statement avoids this loop).

My code is as below, but the loop just keeps on going for any number
entered...

Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"

qvalue = InputBox(Message, Title, Default)

Do Until QCount = qvalue
QCount = QCount + 1
'my code here
Loop

Any ideas?
Thanks,
Ewan.

 

[Guest]

Hi,

Your inputbox is returning a text string so qvalue will never equal qcoount
hence the endless loop. Try this:-


Sub sonic()
Dim Message, Title, Default
Dim qvalue As Integer
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = 1

qvalue = InputBox(Message, Title, Default)

Do Until qcount = qvalue
qcount = qcount + 1
'my code here
Loop
MsgBox qcount
End Sub

Mike

 

[Guest]

Hi Mike,

Sorry, I should have included that I have already declared qvalue as an
integer variable and it still does not work. The inputbox value (qvalue)
works in an if statement before the loop, and there is a validation also to
check the value entered does not exceed 4, with a msgbox resulting if it
does. It just doesn't work in this loop...

Any ideas?
Thanks.

 

[Guest]

Hi,

Then you are going to have to clarify what you mean by

It just doesn't work in this loop...

The code I posted works, if your doesnt then something in your code may be
changing the value of qvalue so that it and Qcount can neve be equal.

Mike

 

[Guest]

Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"


Do
qvalue = val(InputBox(Message, Title, Default))
Loop while (qvalue < 1) and (qvalue > 4)

'my code here

 

[Guest]

Hi Mike,

I've posted the section of code below so you can check if I am overlooking
something. Everything works until the loop is entered, from where it never
exits.

'On Error GoTo ErrorHandler
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect

Dim deptsheet, homefile, Qbook, Q1sheet, Q2sheet, Q3sheet, Q4sheet As Variant
Dim qvalue, QCount, sheetcount As Integer

homefile = ActiveWorkbook.Name

Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"
qvalue = InputBox(Message, Title, Default)

If qvalue > 4 Then
MsgBox "Sorry, incorrect quarter entered. Please try again", vbOKOnly,
"Error!!"
Exit Sub
Else
If qvalue = 1 Then
sheetcount = Workbooks(homefile).Sheets.Count
Application.ScreenUpdating = True
MsgBox "Please select the Q" & qvalue & " file", vbOKOnly, "Select Q" &
qvalue & " File"
Qbook = Application.GetOpenFilename()
a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & qvalue & "
file")
If a = vbNo Then
'GoTo ErrorHandler

Else
If a = vbCancel Then
'GoTo ErrorHandler

End If
End If
Application.ScreenUpdating = False
Workbooks.OpenText Qbook
Qbook = ActiveWorkbook.Name
ActiveWorkbook.Unprotect
Sheets(1).Select
deptsheet = ActiveSheet.Name

Windows(Qbook).Activate
Sheets(deptsheet).Select
Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount)
ActiveSheet.Name = "Dept Card Q" & qvalue

Dim Links As Variant
Dim i As Integer
ActiveSheet.Unprotect
With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With
ActiveSheet.Protect
Workbooks(Qbook).Close savechanges:=False
Workbooks(homefile).Activate
'Sheets(1).Delete

Else

QCount = 0
Do Until QCount = qvalue
QCount = QCount + 1
sheetcount = Workbooks(homefile).Sheets.Count
Application.ScreenUpdating = True
MsgBox "Please select the Q" & QCount & " file", vbOKOnly, "Select Q" &
QCount & " File"
Qbook = Application.GetOpenFilename()
a = MsgBox("Open " & Qbook & "?", vbYesNoCancel, "Open Q" & QCount & "
file")
If a = vbNo Then
'GoTo ErrorHandler

Else
If a = vbCancel Then
'GoTo ErrorHandler

End If
End If
Application.ScreenUpdating = False
Workbooks.OpenText Qbook
Qbook = ActiveWorkbook.Name
ActiveWorkbook.Unprotect
Sheets(1).Select
deptsheet = ActiveSheet.Name

Windows(Qbook).Activate
Sheets(deptsheet).Select
Sheets(deptsheet).Copy After:=Workbooks(homefile).Sheets(sheetcount)
ActiveSheet.Name = "Dept Card Q" & QCount

ActiveSheet.Unprotect
With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With
ActiveSheet.Protect
Workbooks(Qbook).Close savechanges:=False

Loop
End If
End If

etc etc etc

 

[Guest]

Sub ABC()
Dim QCount As Long, qValue As Long
Dim Ans As Variant
Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"

Ans = Application.InputBox(Message, _
Title, Default, Type:=2)
If TypeName(Ans) = "Boolean" Then Exit Sub
qValue = Int(Ans)
If qValue < 1 Or qValue > 4 Then
MsgBox "Must be between 1 and 4 inclusive"
Exit Sub
End If
QCount = 0
Do
QCount = QCount + 1
Debug.Print QCount
'my code here
Loop Until QCount = qValue

End Sub


worked for me.

 

[Guest]

Thanks Joel!!

Dim Message, Title, Default
Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"


Do
qvalue = val(InputBox(Message, Title, Default))
Loop while (qvalue < 1) and (qvalue > 4)

'my code here

 

[Guest]

It should be an or instead of an and

Loop while (qvalue < 1) or (qvalue > 4)

 

[Guest]

If I just copy your loop code:

Sub EFG()
qValue = 4
QCount = 0
Do Until QCount = qValue
QCount = QCount + 1
Debug.Print QCount

Loop

End Sub

it works fine, so there doesn't appear to be a problem in the loop itself.


I would suggest doing the check at the end of the loop so you don't have to
duplicate your code in two places.

To debug, just put a msgbox inside the loop to display the values of
interest (QCount and qValue)

I see you have errhandler instructions commented out - if you are testing
with no error handling and getting the problem, then it probably isn't your
error handling, but if the error handling is being used in your testing, it
could be a contributor.

 

[Guest]

Hi Tom,

Thanks for your reply. I had tried the msgbox method, and it showed that
Qcount was increasing by 1 each time, even though qvalue remained at the
value entered by the user, indicating qvalue was not recognised as an integer
but Qcount was; and thus Excel was comparing apples to pears.

What I did notice was when I hovered the mouse cursor over qvalue and Qcount
in the code during 'break mode' (is this what it's called?) the value for
Qcount would show as a number e.g. 2, but the value for qvalue would show as
a number in speech marks e.g. "2". This has been corrected by adding 'Val'
before the inputbox part of the code. My final solution is as below:

Message = "Please enter the number of the current quarter (1 to 4)"
Title = "Enter Quarter"
Default = "1"
' Display message, title, and default value.
qvalue = Val(InputBox(Message, Title, Default))

If qvalue < 1 Or qvalue > 4 Then
GoTo ErrorHandler 'which says try again - must be quarter 1 to 4
Else
If qvalue = 1 Then
'some code here
Else
'more than one quarter, so create sheets etc for each quarter
'up to the value entered by the user
Do Until QCount = qvalue
QCount = QCount + 1
'more code here
Loop
End If
End If

Thanks again for your input,
Ewan.