VBA Code Needed

[nhbartos]

Hi folks,

I have a table to track Personal, sick, vacation and half days taken for 25 to 150 students.
They are recorded by placing an "S", "P", "V", or an "H" in the cells for the corresponding date.
There is a different tab for each month.
School year July 1 2016 to June 30 2017.

I would like some code to pull the dates for all category entries, for each month, then place them in a table within each students reporting tab and sorted by date.
Below is a partial July 2016 table.

July

Dates of Absence

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Student Name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

Student 1

Student 2

Student 3

Student 4

Student 5

Student 6

Student 7

Student 8

Student 9

Student 10

Student 11

Student 12

Student 13 Is anyone able to help with this? Vince

<colgroup><col><col span="31"></colgroup><tbody>
</tbody>

 

[MickG]

Try this Mini version with new code:-
You'll need to change the Picture path.
https://app.box.com/s/dm5ghget25cg06k10wrcdwd8hrzf14pj

 

[nhbartos]

The dates showed up in the reports in the file you sent.
So, it seems like the first entries worked fine, but then I tested it by:

1. Deleting the letters from july and august and updating.
2. All the dates disappeared. Good.
3. I added new letters in july, student 4, one of each letter, and updated.
4. No dates came up in the reports. I tried it with other students, too. No dates.

5. I deleted the student reports, saved.
6. I added letters, same as #3 above, and updated.
7. No dates.

I don't get it...

The code also did not work in the file I am working on.


Sorry...

 

[nhbartos]

Hi MickG,

I hope you have not given up.
We are so close...

But, if you have had enough, please let me know and I will try to get some help elsewhere.
If this is the case, THANK YOU!

 

[MickG]

I followed your procedure and initially I though I had replicated the problem, but I think I was looking at the wrong sheet.
So I'm not quite sure if I actually found the problem, because I added ".value" to the line " .Cells(c + 18, cc).Value and then the problem seemed not to be there !!!!
I should try altering the line below to explicitly call the Sheet name and the value , as below:-
The problem is quite strange because the headers for the dates in each sheet come from the same array as the dates.

 '##########
        For n = 0 To 10 Step 2 ' added items from dict Now 10 was 7
            cc = cc + 1
            oMax = Application.Max(oMax, Dic(K)(n + 1))
            For c = 1 To Dic(K)(n + 1)
             [B][COLOR=#FF0000]  Sheets(K).Cells(c + 18, cc).Value = [/COLOR][/B]Dic(K)(n)(c) 'NB:- Start Od Dates shown Here as C+18
            Next c
        Next n
  '############# Alter below as required

Also Change the lines here to allow lowercase letters in the Months sheets

 For Ac = 2 To UBound(Ray, 2)
                        If Not IsEmpty(Ray(n, Ac)) Then
                            Select Case [B][COLOR=#FF0000]UCase(Ray(n, Ac))[/COLOR][/B]
                                Case "S": Q(1) = Q(1) + 1: Q(0)(Q(1)) = Dt(sht, CLng(Ray(3, Ac)))
                                Case "E": Q(3) = Q(3) + 1: Q(2)(Q(3)) = Dt(sht, CLng(Ray(3, Ac)))
                                Case "L": Q(5) = Q(5) + 1: Q(4)(Q(5)) = Dt(sht, CLng(Ray(3, Ac)))
                                Case "V": Q(7) = Q(7) + 1: Q(6)(Q(7)) = Dt(sht, CLng(Ray(3, Ac)))
                                Case "H": Q(9) = Q(9) + 1: Q(8)(Q(9)) = Dt(sht, CLng(Ray(3, Ac)))
                                Case "M": Q(11) = Q(11) + 1: Q(10)(Q(11)) = Dt(sht, CLng(Ray(3, Ac)))
                            End Select
                        End If
                    Next Ac
                  Dic(Ray(n, 1)) = Q
            Else
                    Q = Dic(Ray(n, 1))
                    For Ac = 2 To UBound(Ray, 2)
                        If Not IsEmpty(Ray(n, Ac)) Then
                            Select Case[B][COLOR=#FF0000] UCase(Ray(n, Ac))[/COLOR][/B]
                             Case "S": Q(1) = Q(1) + 1: Q(0)(Q(1)) = Dt(sht, CLng(Ray(3, Ac)))
                             Case "E": Q(3) = Q(3) + 1: Q(2)(Q(

 

[nhbartos]

The second set of code looks like not all of it was copied. "Also Change the lines here to allow lowercase letters in the Months sheets".
Take a look and advise.
THANKS!

 

[MickG]

In two places in the code there is "Select Case UCase(Ray(n, Ac))" they originally did not have the "Ucase" bit, so if you add the "Ucase" to those lines you should be able to put both Lower and Upper case letters in the "Months Sheets".

Hope that's what you where referring to.!!!

 

[nhbartos]

Ok.
I pasted this in the two places, lines 23 and 38: Select Case UCase(Ray(n, Ac)

But, I got a compile error highlighting line 12: If Not Dic.Exists UCase(Ray(n, 1)) Then

 

[MickG]

I far as I'm aware that line is not in the code:-
The code Line is actually:-

For n = 4 To UBound(Ray, 1)
            [B][COLOR=#ff0000] If Not Dic.Exists(Ray(n, 1)) Then[/COLOR][/B]
                    ReDim S(1 To 1500): S(1) = "Sick Days"

There is no "Ucase" on that line. The "Ucase's" refer to two "Select cases"

 

[nhbartos]

Ok, updated that, and got a syntax error on line 23.
Select Case UCase(Ray(n, Ac)

Line 1: Sub StuUpdate() is highlighted yellow.

 

[MickG]

Perhaps you should have my updated version of the mini File:-
https://app.box.com/s/5h03txlqs4071xc925w01eyotizq4xf7

NB:- If you update and run the code from the "July_2016" sheet , then at the bottom row 19 on you will see the update of the Leave Dates that should be happening to the Students sheet, this will give an idea of what the code is doing.

You will need to remove those rows Physically each time, to see new updates , as those lines are just for testing.
To remove the actual code see Red line below:-
NB:- The actual Code is now in "Module1"

 For c = 1 To Dic(K)(n + 1)
              [B][COLOR=#FF0000] Sheets("July_2016").Cells(c + 18, cc + 1) = Dic(K)(n)(c)[/COLOR][/B]
               Sheets(K).Cells(c + 18, cc).Value = Dic(K)(n)(c) 'NB:- Start Od Dates shown Here as C+18
            Next c