Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon

Marcia · #1 11-25-2018, 02:49 AM

Big thanks for this cal, but the file is protected.

Got it. I'll get back to you soonest.

Marcia · #2 11-25-2018, 06:17 AM

This is brilliant Cal, impressive. It is a far cry from the usual method of shading the shapes with crayons and calculating the daily learners movement with a calculator. My teacher friends will feel they have travelled to the moon and back once they got the hang of preparing their monthly reports through this automated system. I'v tried all the possible scenarios and I think everything is covered by the dictionary. The built in auto correction of any possible erroneous entry is what makes the system more amazing. Thank you.

A bit of correction in the formulae in AK and AL. Each instance of tardy is counted as 1 not .5, so AM tardy is 1, PM tardy is 1, AM and PM tardy is 2, while cutting class is counted as .5 like absences, so AM cutting class is .5, PM is .5, AM and PM is 1. cutting class is added to absences in AK. I'm sorry about this.

p45cal · #3 11-25-2018, 06:49 AM

Quote:

Originally Posted by Marcia

A bit of correction in the formulae in AK and AL. Each instance of tardy is counted as 1 not .5, so AM tardy is 1, PM tardy is 1, AM and PM tardy is 2, while cutting class is counted as .5 like absences, so AM cutting class is .5, PM is .5, AM and PM is 1. cutting class is added to absences in AK. I'm sorry about this.

Try changing the EmbellishCell macro to:

Code:

Sub EmbellishCell(cll, x, z, Absences, Lates)
CuttingSizeAM = 1: CuttingSizePM = 1
If x(Application.Match("Absent AM", z, 0) - 1) = True Then Absences = Absences + 0.5
If x(Application.Match("Absent PM", z, 0) - 1) = True Then Absences = Absences + 0.5
If x(Application.Match("Late AM", z, 0) - 1) = True Then
  AddTriangle cll, "AM", "LATE", 1
  Lates = Lates + 1
  CuttingSizeAM = 0.5
End If
If x(Application.Match("Late PM", z, 0) - 1) = True Then
  AddTriangle cll, "PM", "LATE", 1
  Lates = Lates + 1
  CuttingSizePM = 0.5
End If
If x(Application.Match("Cutting AM", z, 0) - 1) = True Then
  AddTriangle cll, "AM", "CUTTING", CuttingSizeAM
  Absences = Absences + 0.5
End If
If x(Application.Match("Cutting PM", z, 0) - 1) = True Then
  AddTriangle cll, "PM", "CUTTING", CuttingSizePM
  Absences = Absences + 0.5
End If
End Sub

Marcia · #4 11-25-2018, 06:54 PM

Thank you again Cal. I feel guilty monopolizing your time in this forum. this thread had gone 4 pages already. I can't thank you enough so God bless you. I created a userform for data entry and I am now trawling the web for codes that would make the command buttons run.

Marcia · #5 11-26-2018, 06:27 PM

Hi there.. I copied the enhanced macro that Cal posted for the SF2 into my own workbook. I tried creating a quick button for "reset" by inserting a shape >> assign macro>> then selected reset in the macro list but it jumped to a new module instead of the module in which I pasted the codes. I cancelled the new module, copied the "reset" button of Cal's worksheet then pasted it in my own. But when I clicked on the reset button, it opened Cal's file. How do I create a button from the "reset codes" of my own sheet?

p45cal · #6 11-26-2018, 07:07 PM

Quote:

Originally Posted by Marcia

Hi there.. I copied the enhanced macro that Cal posted for the SF2 into my own workbook.

You did paste it into a standard code-module didn't you? (Not a sheet's code-module, and not the ThisWorkbook code-module.)

Quote:

Originally Posted by Marcia

I tried creating a quick button for "reset" by inserting a shape >> assign macro>> then selected reset in the macro list but it jumped to a new module instead of the module in which I pasted the codes.

I don't understand 'jumped to a new module'

Quote:

Originally Posted by Marcia

I cancelled the new module,

'cancelled'?! What does this mean?

Quote:

Originally Posted by Marcia

How do I create a button from the "reset codes" of my own sheet?

There are 3 ways you can try to be sure that you're choosing the right macro in that dialgue box:
1. Choose This Workbook in the Macros in: field:

2. Close all workbooks except the one you're working on.
3. Rename the copied macro to say ResetMe then you'll be able to identify it for certain in that dialogue box.

(Who's Cal? He seems to just beat me to it each time, and yet I can't see any of his posts.)

Marcia · #7 11-27-2018, 09:38 AM

understand 'jumped to a new module' - When I clicked Assign macro, a new module is opened where i was asked to enter the codes

'cancelled'?! What does this mean? - I deleted the automatic "private sub" codes in the new module above

Rename the copied macro to say ResetMe then you'll be able to identify it for certain in that dialogue box. - The ResetMe name did it. The ResetMe button is now running okay.

(Who's Cal? He seems to just beat me to it each time, and yet I can't see any of his posts.)[/quote] - I am so embarrassed by my assumption that I could shorten your username to Cal. I feel uncomfortable communicating to somebody without mentioning for once the name of the person I am having a conversation with. I'm very sorry if i overstepped the bounds by seemingly getting too personal with addressing you as Cal.

p45cal · #8 11-27-2018, 03:49 PM

Quote:

Originally Posted by p45cal

(Who's Cal? He seems to just beat me to it each time, and yet I can't see any of his posts.)

Don't take this too seriously.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
New learner need help - convert tables in word format to ppt slides.	sas	Word VBA	5	09-21-2017 08:33 PM
Good morning/afternoon/evening based on time	dwirony	Word VBA	1	01-03-2017 01:01 PM
Need conditional formatting formula	namedujour	Excel	1	05-27-2016 08:58 AM
Conditional Formatting Formula	teza2k06	Excel	1	08-22-2014 02:49 AM
Conditional Formula	UICOMP	Excel	1	05-18-2012 12:59 PM