PDA

View Full Version : Help big project due



bigal80ak
05-01-2014, 01:58 PM
Ok so I am doing fine till I get to the last part I am completely lost. If you could figure out what he wants or give me an idea what to do I would appreciate it thanks. Might need to send you attachment so you can really understand.

1. You now select and inspect the "Section 4" worksheet and you open the VB editor to inspect the
macro code associated with this worksheet, which includes a VBA module with a "FlagQuotas" sub
procedure. This is a slightly modified version of the example presented in our courseware slides.
Claire needs your help to adjust the code to accommodate some new business rules logic that allows
for totals information. Row 16 includes a "Total MBE" ("management by exception") label and
adjacent cells designed to house a total count for each ticket holder's equal to or exceeded quota value
occurrences. For example, if the Natalie C's entries had 6 of 12 cell values flagged as being >=
quotas, a value of 6 should be assigned to the range of E16.
2. Modify the existing code in FlagQuotas accordingly to increment a counter variable for each ticket
holder, but only when one of their cell values has been flagged as being greater than or equal to a
quota.
Note that comments have been included in the sub procedure to assist you in knowing
where to insert your code and advice on how to handle the processes. Each comment begins with
"PROJECT 3 HINT…". Also, this workflow should be followed:

Integrate proper indenting of all statements.

Modify the existing comments to include your name and code creation date.

Declare 4 new Integer variables, suitably named (i.e. prefix each variable with "int").

Next, incorporate the VBA code to increment each of these 4 variables at the right time (i.e. when
the quota has been met or exceeded). This would involve creating a Select Case statement that
determines which ticket holder column the active cell is in, and then update his/her variable.

Once the For…Next loop completes, set each of the "Total MBE" cells (D16:G16) with its
matching variable value. Here's an example (note that sample data is shown; your values may
vary) of what Section 4 looks like after the above logic has been implemented (9 for Jack M., 6
for Natalie C., etc…)…
Page 6 of 7
3. Modify the MsgBox statement that ends the sub procedure to include the total count of MBE values.
For example, if Jack M. had 9, Natalie C. had 6, Anna M. had 5, and Bob T. had 5, the message box
would be display…
4. Test/debug your VBA code to ensure correctness. Click the "Evaluate Ticket Quotas" button to
perform your test. This button is already assigned to a macro that will assign new values to the
worksheet and call the FlagQuotas sub procedure.
5. Make sure the worksheets are in the following order (left to right):

Section 1

Section 2

Section 3

Section 4
6. Activate the "Section 1" worksheet.
7. Make A1 the active cell and ensure the Normal view is selected.
8. Repeat this process for all worksheets.
9. Activate the "Section 1" worksheet.
10. Re-save the file using the default name.
Page 7 of 7
11. Close all open files and exit Excel.
12. Upload all project file(s) via the class site's "Assignments" (digital dropbox) link. To perform the
upload, click the link. Then, follow the prompts to upload and submit your file(s). Remember to
retain your original files - do not delete them - just in case I need you to make a re-submission. After
the upload occurs, you can confirm your submission by returning to the dropbox link and reviewing
the "Status: Submitted on…" message.
13. Close any open files and exit all applications.

Jan Karel Pieterse
05-02-2014, 02:27 AM
Are you looking for professionals to run this project for you?
I ask because that is the impression I get from your message, containing all sorts of directives and wishes.

This forum is run by volunteers and is intended for asking specific questions, not for creating entire projects.

p45cal
05-02-2014, 03:37 AM
From the original:"Instructions
This project measures your knowledge of Excel 2010.
It includes tasks that have been presented in our
textbook, online resources, etc. covering the module 3 content. You may utilize your textbook, class
site(s), online help, and notes. All work must be done
on an individual basis; no assistance from others is
allowed and an honesty policy is assumed. If any questions arise as you work on the project, email me at
least 24 hours before the submission deadline. "

It does say you may use online help, but I suspect that means Excel's online help.
Let's keep it quiet from Mark Choman.
http://academic.luzerne.edu/mchoman/proj3/cis112-701_project3.pdf

snb
05-02-2014, 03:54 AM
Has to be delivered in 2011 .....

bigal80ak
05-02-2014, 04:40 AM
Has to be delivered in 2011 .....

I have a tutor she was able to go over the first 3/4 parts with me. When it came to this she said you should really be asking someone else as I don't feel comfortable doing this. This seems to be more programming then Excel. Personnally read the chapter twice don't understand any of it. Gave it a shot but idk if it is right without having someone look at the project and say no that's not right. It should look like this .....
I have to do it for 4 different things. So just showing or helping me with the first I should be able to figure out the other 4. Its hard to show without seeing the project sorry. It seems like I am reading something far beyond me I don't know really what to do or where to begin.

Kenneth Hobs
05-02-2014, 05:46 AM
Most forums have a policy about doing homework for others. http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item

With that said, if you can illustrate a concept and separate it from the coursework, and need help, then it would be appropriate to ask. That is the challenge of learning and problem solving, to find the concept and learn that. In engineering, the first part is to fully define the problem with all knowns given. Break out each part and then solve it.

As the instructor said, if you need clarification for the homework, ask him. If you ask him how to solve it, he should say no as would be expected.

bigal80ak
05-02-2014, 06:51 AM
Ok thanks for finally getting some help I re-edited my original post all I need help with are the first half of section 4 which is now in original post. I found where he wanted me to go I open the worksheet and go to VBA and this comes up and you find the part that says Project 3 hint and that's where you are supposed to enter code I think I did what he wanted in part one or at least tried. But this should give you some idea of what he wanted. I will put in BOLD what I did. Right above it will be his directions.
Public Sub EvaluateQuotas()
EstablishQuotas
FlagQuotas
End Sub
Sub EstablishQuotas()
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,300)"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4:G4"), Type:=xlFillDefault
Range("D4:G4").Select
Selection.AutoFill Destination:=Range("D4:G15"), Type:=xlFillDefault
Range("D4:G15").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
Public Sub FlagQuotas()
' Developer: (John Yokum)
' Creation date: (4/29/2014)
Dim rngActiveCell As Range
Dim blnExhibition As Boolean
Dim blnRegSeason As Boolean
Dim blnPlayoffs As Boolean
' PROJECT 3 HINT... On 4 lines following this comment, declare 4 integer
' variables, each named after the ticket holder (i.e. intJackM, intNatalieC,
' intAnnaM, and intBobT). These are used to count the occurrences for each
' ticket type that is flagged according to its ticket holder.
intJackM [D16=D4:D15>=C18,C19,C20]
intNatalieC [E16=E4:E15>=C18,C19,C20]
intAnnaM [D16=F4:F15>=C18,C19,C20]
intBobT [D16=G4:G15>=C18,C19,C20];
[/SIZE][/COLOR]
[/COLOR]
' Clear the range of tickets sold of any formats.
Range("TicketsSold").ClearFormats

' Loop through the tickets sold range. Examine each cell, one
' at a time. Determine its ticket type using its row number.
For Each rngActiveCell In Range("TicketsSold")
Select Case rngActiveCell.Row
Case 4, 7, 10, 13
blnExhibition = True
blnRegSeason = False
blnPlayoffs = False
Case 5, 8, 11, 14
blnExhibition = False
blnRegSeason = True
blnPlayoffs = False
Case 6, 9, 12, 15
blnExhibition = False
blnRegSeason = False
blnPlayoffs = True
Case Else
MsgBox "Error in rngActiveCell.Row." & _
"Value of rngActiveCell.Row: " & _
rngActiveCell.Row
Exit Sub
End Select

' Now that we know the active cell's ticket type, check to see if it's
' equal or greater than its designated quota.
' Exhibition...
If blnExhibition = True Then
If rngActiveCell.Value >= Range("ExhibQuota") Then
rngActiveCell.Interior.Color = vbYellow
' PROJECT 3 HINT... Now that a cell has been flagged, determine
' the current ticket holder using the rngActiveCell.Column property
' in a SELECT CASE statement. Knowing the column tells you which
' ticket holder variable you need to add 1 to. Note that the "Column"
' property is being returned as a number, not a letter. For example,
' if the column is D, the Column property returns 4 and your SELECT CASE
' statement will check for the 4 and increment intJackM by 1.
GoTo LoopingMarker
End If
End If
' Regular season...
If blnRegSeason = True Then
If rngActiveCell.Value >= Range("RegQuota") Then
rngActiveCell.Interior.Color = vbYellow
' PROJECT 3 HINT... Now that a cell has been flagged, determine
' the current ticket holder using the rngActiveCell.Column property
' in a SELECT CASE statement. Knowing the column tells you which
' ticket holder variable you need to add 1 to. Note that the "Column"
' property is being returned as a number, not a letter. For example,
' if the column is D, the Column property returns 4 and your SELECT CASE
' statement will check for the 4 and increment intJackM by 1.
GoTo LoopingMarker
End If
End If
' Playoffs...
If blnPlayoffs = True Then
If rngActiveCell.Value >= Range("PlayQuota") Then
rngActiveCell.Interior.Color = vbYellow
' PROJECT 3 HINT... Now that a cell has been flagged, determine
' the current ticket holder using the rngActiveCell.Column property
' in a SELECT CASE statement. Knowing the column tells you which
' ticket holder variable you need to add 1 to. Note that the "Column"
' property is being returned as a number, not a letter. For example,
' if the column is D, the Column property returns 4 and your SELECT CASE
' statement will check for the 4 and increment intJackM by 1.

GoTo LoopingMarker
End If
End If
LoopingMarker:
' Return to the beginning of the loop to get the next active cell in
' the range of tickets sold.
Next

' PROJECT 3 HINT... Now that the range of tickets sold has been evaluated and each
' ticket holder variable incremented, you need to present each ticket holder variable
' value. One the 4 lines following this comment, assign each ticket holder variable
' to its matching "MBE" cell. For example, if JackM had 5 cells flagged in yellow,
' the intJackM variable would contain a value of 5. So, assign intJackM to the range of D16.


' PROJECT 3 HINT... This section of the sub procedure presents a grand total of the
' ticket holder variables within a message box. Modify the existing message box to
' include this grand total by adding each of the ticket holder variables.
MsgBox ("Processing completed!" & _
vbCrLf & _
"The grand total of 'MBE' cells is: " & _
vbCrLf & _
"Thanks for your business.")
End Sub