FOR NEXT LOOP QUESTION

[dabluebery]

Hey,

I'm in a VBA class, and I have a simple HW assignment that I don't quite understand. Here's the question;

Write a sub that asks for a product index from 1 to 100. Embed this within a Do loop so that the user keeps being asked until he enters an integer from 1 to 100. (use a For loop for checking)

I don't understand how I would use a for loop to check if the number is an integer. I understand the first part of the question, and already have a do loop checking if the value entered is numeric. If not, it prompts an input box until you do. Any insight is appreciated.

Rob

 

[Legacy 21301]

Post what code you have so far.

 

[SIXTH SENSE]

try this.
for i=1 to 100
if inputX=i then
msgbox "input is valid!!! "
exit for
elseif inputX<>i and i=100 then
msgbox "input is invalid!!!!! "
end if
next i

 

[Nimrod]

First of all its a good idea to use "Application.inputbox" versus "inputBox" as "Application.inputbox" allows you to specify what kind of data is allowed to be entered. IN the example below you can see the parmeter " Type:=1" this mean that only numbers will be allowed. If you type in letters or special charcters the following code will give you an error message and send you back into the loop.

The Response = Int (Response ) in the loop makes sure the number is an integer.

So the following code test to make sure the Entry is a number and an integer. If it isn't then you don't get to exit the Do...loop ! :wink:


Public Sub AskForNum()
Do
Response = Application.InputBox("Please Enter Integer Between 1 and 100", "Enter Number", Type:=1)
Loop Until Reponse > 0 And Response < 101 And Reponse = Int(Response)
End Sub

 

[Nimrod]

OOOOOOPSSSSS A TYPO CREPT INTO MY CODE ABOVE It's been corrected in the following code ..

Public Sub AskForNum()
Do
Response = Application.InputBox("Please Enter Integer Between 1 and 100", "Enter Number", Type:=1)
Loop Until Response > 0 And Response < 101 And Response = Int(Response)
End Sub

 

[Legacy 21301]

OOOOOOPSSSSS A TYPO CREPT INTO MY CODE ABOVE It's been corrected in the following code ..

Public Sub AskForNum()
Do
Response = Application.InputBox("Please Enter Integer Between 1 and 100", "Enter Number", Type:=1)
Loop Until Response > 0 And Response < 101 And Response = Int(Response)
End Sub

But what about dates from 1 Jan 1900 to 9 April 1900 ?

 

[Nimrod]

Heres an Upgraded version of my last posting ... This version has the following features:
1. uses type:=1 to check that entry is number
2. will exit loop if the cancel button on input box is selected eg. if Response = False
3. Changes the input message if you make wrong entry
4. Checks response is an integer using "Response = Int(Response) "
5. additional Error message will popup with if entry is left blank or has invalid entry .



Public Sub AskForNumV2()
Do
Response = Application.InputBox(agn & "Please Enter Integer Between 1 and 100", "Enter Number", Type:=1)
If Response = False Then Exit Sub
agn = "Sorry the entry... " & Response & " ..is invalid" & vbCrLf
Loop Until Response > 0 And Response < 101 And Response = Int(Response)
End Sub



Note: Mr. Nob posted and asked a question about dates.... I have no idea what your question means since we're not dealing with dates here ???

 

[Smitty]

I think Mr. Nob meant what happens if someone enters 1/1/1900 and the system interprets it as "1", same with 4/9/1900 being "100".

 

[Legacy 21301]

Since Mr. Nimrod has not re-appeared to address my query (clarified by Mr. pennysaver), here is a suggested amendment to Mr. Nimrod's code.

The amended code will accept input of the numbers 1 to 100 only, and prevents (will not accept) dates.

Public Sub AskForNumV2()
Do
response = Application.InputBox(agn & "Please Enter Integer Between 1 and 100", "Enter Number")
If response = False Then Exit Sub
agn = "Sorry the entry... " & response & " ..is invalid" & vbCrLf
Loop Until Val(response) > 0 And Val(response) < 101 And response = Int(Val(response)) And Not IsDate(response)
MsgBox "You successfully entered " & Val(response)
End Sub

 

[Nimrod]

I'm on the road for the next few day's but have checked in. In response to Mr. Nobs concerns about entry of dates , I don't see how dates are a concern as the Type:= 1, prevents anything but numbers being entered into the input box. This "feature" of the input box has been explained in my previous postings ... OK ?