PDA

View Full Version : Help with a while wend loop



ccknight
09-23-2007, 07:47 PM
Does anyone know how to complete the following in the VBA editor. I am having alot of trouble with this and help would be greatly appreciated.




1. Prompt the user to enter the number of random numbers between 20 and 100 to be generated.

2. Use a While/Wend loop to generate the random numbers, and write them to A1:A100.

3. Calculate average of the random numbers; write a label in C1 and the average in D1; also the average should be rounded to a whole number.

4. Create a private function called Compare that has two arguments: The random number and the average. The function will return an integer. The function will test three conditions:
1. Number is greater than average, return a 1.
2. Number is equal to average, return a 2.
3. Number is less than the average, return a 3.

5. Since each number must be passed to Compare, a While/Wend loop should be constructed first.

6. With in the body of the loop, an If/Then/ElseIf statement will be required to invoke the function; pass random number and pass average; determine if a 1, 2, or 3 was returned from Compare function.

7. If the result is a 1, a label stating the number is greater than the average should be written to the sheet. If the result is 2, a label stating the number is equal to the average should be written to the sheet. If the result is 3, a label stating the number is less than the average should be written to the sheet.

8. Create a Clear Module that will clear the spreadsheet.

TonyJollans
09-23-2007, 11:27 PM
If this is not homework, I don't know what is.

Bob Phillips
09-24-2007, 12:42 AM
.1 Prompt the user to enter the number of random numbers between 20 and 100 to be generated

Look at Input box in Help.



2 Use a While/Wend loop to generate the random numbers, and write them to A1:A100

Look at While and Rnd in help.



3 Calculate average of the random numbers; write a label in C1 and the average in D1; also the average should be rounded to a whole number

Work out how you would do it on paper, and code that.



4 Create a private function called Compare that has two arguments: The random number and the average. The function will return an integer. The function will test three conditions:.
.1. Number is greater than average, return a 1..
.2. Number is equal to average, return a 2..
.3. Number is less than the average, return a 3

Work out how you would do it on paper, and code that.



5 Since each number must be passed to Compare, a While/Wend loop should be constructed first

Use info from 2.



6 With in the body of the loop, an If/Then/ElseIf statement will be required to invoke the function; pass random number and pass average; determine if a 1, 2, or 3 was returned from Compare function

Look at If in Help.



7 If the result is a 1, a label stating the number is greater than the average should be written to the sheet. If the result is 2, a label stating the number is equal to the average should be written to the sheet. If the result is 3, a label stating the number is less than the average should be written to the sheet

Look at Range in Help, especially the Value property.



8 Create a Clear Module that will clear the spreadsheet..

Do it in Excel using the macro recorder.