Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #46  
Old 11-25-2018, 06:49 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
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

Reply With Quote
  #47  
Old 11-25-2018, 06:54 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #48  
Old 11-26-2018, 06:27 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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?
Reply With Quote
  #49  
Old 11-26-2018, 07:07 PM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
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 View Post
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 View Post
I cancelled the new module,
'cancelled'?! What does this mean?





Quote:
Originally Posted by Marcia View Post
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.)
Reply With Quote
  #50  
Old 11-27-2018, 09:38 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #51  
Old 11-27-2018, 03:49 PM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by p45cal View Post
(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.
Reply With Quote
  #52  
Old 11-30-2018, 04:26 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
Don't take this too seriously.

Yeah, I know. Can i post a picture in this thread? I would like to post a picture of the core group of teachers from the public schools and mission-oriented teachers of an Anglican Church school who are being trained in the program that this forum helped create.
Reply With Quote
  #53  
Old 11-30-2018, 04:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 64bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Marcia
click your UserCP. In the left column you have " Pictures and albums". You can add your pictures there ( Frankly I never used it, so any tips are welcome ) or create album(s)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #54  
Old 11-30-2018, 04:37 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
Marcia
click your UserCP. In the left column you have " Pictures and albums". You can add your pictures there ( Frankly I never used it, so any tips are welcome ) or create album(s)

Thanks. I tried opening this forum in my cellphone where the pics are stored but I couldn't find the box where I need to enter the password. There's only the username in it.
Reply With Quote
  #55  
Old 02-14-2019, 09:03 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I moved this post to Excel Programming.

Last edited by Marcia; 02-15-2019 at 05:29 PM.
Reply With Quote
  #56  
Old 10-15-2019, 07:31 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Hi all. The teachers who are the users of the attached code, courtesy of this Forum, have been happily using it for more than a year. Now, they are requesting for enhancements as follows:
1. That "Sick" be added to differentiate it from an attendance record that is just plain truant absent. "Sick" should be colored green.
2. In the totals column (Col AC), Sick should be treated as absent for half day (.5) or whole day (1).
3. In AH92 and AI92, the total incidence of at least 5 consecutive absences should now include in the formula the count of at least 5 consecutive absences due to illness.
4. After the update, the diagonal lines should be invisible for a neater look.
This is a repost but number 3 and 4 are added.
Thank you.
Attached Files
File Type: xlsm FORM.SY 2019-2020 - Copy.xlsm (297.5 KB, 5 views)
Reply With Quote
  #57  
Old 10-16-2019, 06:59 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Unfortunately I had already done most of the work to update the code before you attached your file in the last message, so the work was done on the old file. I attach that file and leave it to you to do the remaining adjustments.


I have added a bunch (60 ish) dictionary entries - you'll have to compare these with your own and choose what to keep.
Note that I've addded two columns and changed "" to Empty and "TRUE" to True. This is more robust.



Regarding the diagonal lines in cells, this is used (partly) to determine which cells to process so I haven't deleted them, only changed their colour. I restore their visibility with the Reset button. This is so that users can see that a given cell could be a candidate for processing. The code only changes the colour, not the thickness/dottedness etc. so you can choose those aspects manually independently.


In the code line that colours the green triangles I've included a range of shades of green for you to experiment with:
Code:
myshape.Fill.ForeColor.RGB = 31261  '5296274,3969910,10213316,5287936,31261,vbGreen    '(different greens to try)
I've done nothing about any formulae on the sheet at all.
Attached Files
File Type: xlsm msofficeforums40597JUNE 2018 QUERYBoffer03.xlsm (62.7 KB, 5 views)
Reply With Quote
  #58  
Old 10-17-2019, 04:55 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Wonderful. I changed the "S" to "K" due to the formulae in rows 44 and 74 that use "SI" for shifted in learners.
"Sick" absences should also be included in the below code that count the number of learners with more than 5 consecutive absences. Please help again,
Code:
Function CountOfRowsWithNOrMoreConsecutiveXs(myRange, N)
Vals = myRange.Value
For rw = 1 To UBound(Vals)
  ThisRowCount = 0
  For colm = 1 To UBound(Vals, 2)
    If InStr(1, UCase(Vals(rw, colm)), "X") > 0 Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
    If ThisRowCount >= N Then
      RowCount = RowCount + 1
      Exit For
    End If
  Next colm
Next rw
CountOfRowsWithNOrMoreConsecutiveXs = RowCount
End Function
Thank you.
Attached Files
File Type: xlsm FORM.SY 2019-2020 - Copy.xlsm (144.8 KB, 5 views)
Reply With Quote
  #59  
Old 10-17-2019, 05:26 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Untested:
Code:
If (InStr(1, UCase(Vals(rw, colm)), "X") > 0 Or InStr(1, UCase(Vals(rw, colm)), "K") > 0) Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
Reply With Quote
  #60  
Old 10-17-2019, 02:29 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
Untested:
Code:
If (InStr(1, UCase(Vals(rw, colm)), "X") > 0 Or InStr(1, UCase(Vals(rw, colm)), "K") > 0) Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
Perfect. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Good morning/afternoon/evening based on time dwirony Word VBA 1 01-03-2017 01:01 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Need conditional formatting formula namedujour Excel 1 05-27-2016 08:58 AM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formatting Formula teza2k06 Excel 1 08-22-2014 02:49 AM
Conditional Formula UICOMP Excel 1 05-18-2012 12:59 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft