complicated :( please help

N

new_b

New Member
Joined
Jul 29, 2002
Messages
21
Hi

I have inherited a collegues spreadsheet who has now left the company and it is my job so sort it out, the basics of it is that it calculates everyones holidays, public holidays and shift rotas, anyway I have changed the main data to represent next year but for some reason on a holiday macro its picking up some info thats stopping me recording a holiday, ie its saying that its a public holiday when i know next year it isnt. is there a way to find out where this data is coming from or something?

Please help

Thanks for looking

Technium
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
try hitting ALT+F11 in excel which will bring up the code for the macro in the VB editor.... you can post the code here and i guess someone will be able to make a start on deciphering it for you.

oh, and welcome to the board :)
 
Upvote 0
On 2002-10-01 03:57, new_b wrote:
Hi

I have inherited a collegues spreadsheet who has now left the company and it is my job so sort it out, the basics of it is that it calculates everyones holidays, public holidays and shift rotas, anyway I have changed the main data to represent next year but for some reason on a holiday macro its picking up some info thats stopping me recording a holiday, ie its saying that its a public holiday when i know next year it isnt. is there a way to find out where this data is coming from or something?

Please help

Thanks for looking

Technium
Click to expand...

Also,

Goto Format>Sheet and see if the Unhide function is not greyed out, if not it may be a hidden sheet, unhide any that are there.

If it's greyed out it might still be a hidden sheet.
Goto VBE Alt+F11
On the right hand side, check the explorer to see if there are any sheets that you CAN'T see from the mian Excel screen.
If so you can check in the properties of the sheet to see if they are xlVeryHidden.
You can change this to xlVisable.
 
Upvote 0
ok here is the macro "holiday Form" if i click on it, i get the question "which officer" and I select one and then get error "silly billy, 8 booked on ph 29th March" and I need to know where its getting this info from. thanks

Sub Holiday_Form()

v = 0

With Sheets("Leave Sheet")
.[A4:E4].ClearContents
.[E7].ClearContents
.[D9:E12].ClearContents
.[D16:E22].ClearContents
.[B26:E100].ClearContents
.[A37].ClearContents
.[A26:E100].Interior.ColorIndex = xlNone
End With

Sheets("Data").[C1:E100].ClearContents

With Sheets("Rota")
a = 20
b = 1
Do
If .Cells(2, a) <> "" Then
Sheets("Data").Cells(b, 3) = .Cells(2, a)
Sheets("Data").Cells(b, 4) = .Cells(1, a)
Sheets("Data").Cells(b, 5) = .Cells(2, a) & " " & .Cells(1, a)
b = b + 1
End If
a = a + 1
Loop Until a = 72
End With

With Sheets("Data")
.[C1:E100].Sort key1:=.[D1], order1:=xlAscending, header:=xlNo
.[C1:E100].HorizontalAlignment = xlLeft
End With

With Which_Officer
.offr.ColumnCount = 1
.offr.RowSource = ("Data!E1:E100")
.Show

If v = 1 Then GoTo quitt
officer = .offr
End With

With Sheets("Data")
c = 1
Do Until .Cells(c, 5) = officer
c = c + 1
Loop
off1 = .Cells(c, 3)
off2 = .Cells(c, 4)
End With

Sheets("Leave Sheet").[A4] = officer

grp1 = ""
With Sheets("Data")
d = 1
Do
If .Cells(d, 21) = off2 Then grp1 = .Cells(d, 22)
d = d + 1
Loop Until d = 12
End With

With Sheets("rota")
d = 1
Do Until .Cells(1, d) & .Cells(2, d) = off2 & off1
d = d + 1
Loop

e = d
Do Until .Cells(5, e) <> ""
e = e - 1
Loop
grp2 = Int(Right(.Cells(5, e), 1))
End With

If grp1 = "" Then grp1 = grp2

Sheets("Leave Sheet").[E7] = "GROUP " & grp2

f = 10
Do Until Sheets("Data").Cells(1, f) = grp1
f = f + 1
Loop

g = 10
Do Until Sheets("Data").Cells(1, g) = grp2
g = g + 1
Loop

With Sheets("Leave Sheet")
.[B26] = Sheets("Data").Cells(2, f)
.[B27] = Sheets("Data").Cells(89, f)
.[B28] = Sheets("Data").Cells(92, f)
.[B29] = Sheets("Data").Cells(127, g)
.[B30] = Sheets("Data").Cells(154, g)
.[B31] = Sheets("Data").Cells(155, g)
.[B32] = Sheets("Data").Cells(156, g)
.[B33] = Sheets("Data").Cells(238, g)
.[B34] = Sheets("Data").Cells(239, g)
.[B35] = Sheets("Data").Cells(360, g)
.[B36] = Sheets("Data").Cells(361, g)

.[C26] = Sheets("Rota").Cells(6, d)
.[C27] = Sheets("Rota").Cells(93, d)
.[C28] = Sheets("Rota").Cells(96, d)
.[C29] = Sheets("Rota").Cells(131, d)
.[C30] = Sheets("Rota").Cells(158, d)
.[C31] = Sheets("Rota").Cells(159, d)
.[C32] = Sheets("Rota").Cells(160, d)
.[C33] = Sheets("Rota").Cells(242, d)
.[C34] = Sheets("Rota").Cells(243, d)
.[C35] = Sheets("Rota").Cells(364, d)
.[C36] = Sheets("Rota").Cells(365, d)

c = 26
Do
If .Cells(c, 3) = "" Then
Range(.Cells(c, 3), .Cells(c, 5)).Interior.ColorIndex = 1
.Cells(c, 4) = "X"
.Cells(c, 5) = "X"
End If
c = c + 1
Loop Until c = 37

c = 26
Do
Select Case .Cells(c, 2)
Case "R"
If .Cells(c, 3) = "R" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "PH" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
Range(.Cells(c, 1), .Cells(c, 5)).Interior.ColorIndex = 3
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "PH booked on a R on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "A" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "BL" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5).Interior.ColorIndex = 1
.Cells(c, 5) = "X"
MsgBox "B leave booked on a R on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "LS" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "LS leave booked on a R on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "L" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "S" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8.5 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 24 Then
If off1 = "DO" Then phtot = phtot + 1 Else phtot = phtot + 1.5
End If
c = c + 1
Case 8
If .Cells(c, 3) = "R" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "R booked on an 8 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "PH" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "A" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "BL" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "B leave booked on an 8 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "LS" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "LS leave booked on an 8 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "L" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "S" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8.5 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 24 Then
If off1 = "DO" Then phtot = phtot + 1 Else phtot = phtot + 1.5
End If
c = c + 1
Case 8.5
If .Cells(c, 3) = "R" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "R booked on an 8.5 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "PH" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "A" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "BL" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "B leave booked on an 8.5 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "LS" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "LS leave booked on an 8.5 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "L" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "S" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8.5 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 24 Then
If off1 = "DO" Then phtot = phtot + 1 Else phtot = phtot + 1.5
End If
c = c + 1
Case 24
If .Cells(c, 3) = "R" Then
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
MsgBox "R booked on a 24 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "PH" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "A" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "BL" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
MsgBox "B leave booked on a 24 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "LS" Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
Range(.Cells(c, 1), .Cells(c, 3)).Interior.ColorIndex = 3
MsgBox "LS leave booked on a 24 PH on " & .Cells(c, 1) & "!", vbOKOnly, "SILLY BILLY!"
ElseIf .Cells(c, 3) = "L" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = "S" Then
.Cells(c, 4) = .Cells(c, 1)
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 8.5 Then
.Cells(c, 5) = "X"
.Cells(c, 5).Interior.ColorIndex = 1
ElseIf .Cells(c, 3) = 24 Then
If off1 = "DO" Then phtot = phtot + 1 Else phtot = phtot + 1.5
End If
c = c + 1
Case Else
c = c + 1
End Select
Loop Until c = 37
If off1 = "DO" Then
.[E26:E35] = "X"
.[E26:E35].Interior.ColorIndex = 1
End If
End With

With Sheets("Rota")
a = 6
al = 9
bl = 16
ls = 20
ph = 26
hph = 26
ol = 37
aph = 37

Do
If .Cells(a, 1) <> "" Then mon = .Cells(a, 1)
Select Case .Cells(a, d)
Case "A"
dat = .Cells(a, 2) & " " & mon
b = a + 6
Do
a = a + 1
If .Cells(a, 1) <> "" Then mon = .Cells(a, 1)
Loop Until a = b
dat = dat & " - " & .Cells(a, 2) & " " & mon
Sheets("Leave Sheet").Cells(al, 4) = dat
a = a + 1
al = al + 1
Case "BL"
If bl = 19 Then
MsgBox "Too many B leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a B leave!", vbOKOnly, "ERROR!"
Else
Sheets("Leave Sheet").Cells(bl, 4) = .Cells(a, 2) & " " & mon
bl = bl + 1
End If
a = a + 1
Case "LS"
If ls = 23 Then
MsgBox "Too many Long Service leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a Long Service leave!", vbOKOnly, "ERROR!"
Else
Sheets("Leave Sheet").Cells(ls, 4) = .Cells(a, 2) & " " & mon
ls = ls + 1
End If
a = a + 1
Case "PH"
b = 1
Do
If .Cells(a, 2) & " " & mon = Sheets("Data").Cells(b, 9) Then GoTo loopa
b = b + 1
Loop Until b = 12
Do Until Sheets("Leave Sheet").Cells(ph, 4) = ""
ph = ph + 1
Loop
If ph >= 36 Then
If hph < 36 Then
Do Until Sheets("Leave Sheet").Cells(hph, 5) = ""
hph = hph + 1
If hph >= 36 Then
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a PH!", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = .Cells(a, 2) & " " & mon
aph = aph + 1
GoTo loopa
End If
Loop
Sheets("Leave Sheet").Cells(hph, 5) = "½ " & .Cells(a, 2) & " " & mon
Do Until Sheets("Leave Sheet").Cells(hph, 5) = ""
hph = hph + 1
If hph = 36 Then
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a PH!", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = .Cells(a, 2) & " " & mon
aph = aph + 1
GoTo loopa
End If
Loop
Sheets("Leave Sheet").Cells(hph, 5) = "½ " & .Cells(a, 2) & " " & mon
Else
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a PH!", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = .Cells(a, 2) & " " & mon
aph = aph + 1
GoTo loopa
End If
Else
Sheets("Leave Sheet").Cells(ph, 4) = .Cells(a, 2) & " " & mon
ph = ph + 1
End If
loopa:
a = a + 1
Case ".PH"
Do Until Sheets("Leave Sheet").Cells(hph, 5) = ""
hph = hph + 1
Loop
If hph >= 36 Then
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a half PH", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = "½ " & .Cells(a, 2) & " " & mon
aph = aph + 1
Else
Sheets("Leave Sheet").Cells(hph, 5) = .Cells(a, 2) & " " & mon
hph = hph + 1
End If
a = a + 1
Case 12
Do Until Sheets("Leave Sheet").Cells(hph, 5) = ""
hph = hph + 1
Loop
If hph >= 36 Then
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a half PH", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = "½ " & .Cells(a, 2) & " " & mon
aph = aph + 1
Else
Sheets("Leave Sheet").Cells(hph, 5) = .Cells(a, 2) & " " & mon
hph = hph + 1
End If
a = a + 1
Case "+12"
Do Until Sheets("Leave Sheet").Cells(hph, 5) = ""
hph = hph + 1
Loop
If hph >= 36 Then
MsgBox "Too many PH leaves! " & .Cells(a, 2) & " " & mon & " cannot be taken as a half PH", vbOKOnly, "ERROR!"
Sheets("Leave Sheet").[D37] = "Additional PH's"
Sheets("Leave Sheet").Cells(aph, 5) = "½ " & .Cells(a, 2) & " " & mon
aph = aph + 1
Else
Sheets("Leave Sheet").Cells(hph, 5) = .Cells(a, 2) & " " & mon
hph = hph + 1
End If
a = a + 1
Case 2000
Sheets("Leave Sheet").[A37] = "2000 Leave O/s"
Sheets("Leave Sheet").Cells(ol, 2) = .Cells(a, 2) & " " & mon
ol = ol + 1
a = a + 1
Case Else
a = a + 1
End Select
Loop Until a = 371
End With

Sheets("Leave Sheet").Select

quitt:
End Sub
 
Upvote 0
There is rather a lot of hard coding here, but its seems to me that the public holidays are contained in sheet "Rota" in rows 6, 93, 96, 131, 158, 159, 160, 242, 243, 364 and 365
for each officer.

Have a look there first.
 
Upvote 0
Fantastic that bit worked but now i have another question to do with that code, in the "Leave Sheet" i am getting cells C36 filled in black with nothing in it, and cells D37 and E37 also filled in black but with "X" in it, can someone tell me where this info is coming from so I can delete it as this line is no longer required.

Thanks for all your help.

Technium
 
Upvote 0
This bit?

Code: 
c = 26 
Do 
   If .Cells(c, 3) = "" Then 
      Range(.Cells(c, 3), .Cells(c, 5)).Interior.ColorIndex = 1 
      .Cells(c, 4) = "X" 
      .Cells(c, 5) = "X" 
   End If 
   c = c + 1 
Loop Until c = 37
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Conditional Format Date Range
Replies
4
Views
547
zero269
Z
A
  • Solved
If-then Calculation Across Rows/Columns Based On Other Columns.
Replies
8
Views
852
RobP
R
A
  • Solved
Fill down formulas and Do Until "No more data"
Replies
4
Views
729
felixstraube
F
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
962
jkpieterse
jkpieterse
J
  • Question
Formula to SUM "On Hold" time overlaps (minus non-work time, weekends & public holidays)
Replies
5
Views
707
fjns
F

Forum statistics

Threads
1,214,432
Messages
6,119,468
Members
448,900
Latest member
Fairooza

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top