At work we have a mix of desktop MS Office (various versions) and Office 365. I wrote an Excel macro that works perfectly on my desktop (Office 2010) but fails win I try to run it on Excel Office 365 (v.2108 build 14326.20238). I get a Run Time Error '13' Type Mismatch. I've looked at this awhile. I tried changing variable types, but still no go. I'm think it's probably easy but I just can't see it. If anyone has experience with the differences in these version and can tell me what I'm missing I would be greatly appreciative. Code follows. Highlighted line is where error occurs.
Code:
Sub Update980Off()
' This macro will put employee initials into the proper crew column and the proper 980 Friday off column
Dim Emp As String ' Holds the employees initials
Dim Last980 As Single ' Holds the number of the last used row in the Nine Eighty Employees list
Dim Last1st As Single ' Holds the number of the last used row of first friday employees in column B on 9-80 sheet
Dim Last2nd As Single ' Holds the number of the last used row of second friday employees in column E on 9-80 sheet
Dim n As Single ' This is a loop counter for employee names
Dim c As Single ' This is a loop counter for collections crew
Dim m As Single ' This is a loop counter for maintenance crew
Dim r As Single ' This is a loop counter for repair crew
Dim u As Single ' This is a loop counter for usa/services crew
Dim fL As Single ' This is a loop counter for the friday left column (T,U,V,W)on Duty Schedule sheets
Dim fR As Single ' This is a loop counter for the friday right column (AQ,AR,AS,AT)on Duty Schedule sheets
Dim fc1 As Single ' This variable hold the column number for friday (column T or V)on Duty Schedule sheets
Dim fc2 As Single ' This variable hold the column number for friday (column AQ or AS)on Duty Schedule sheets
Dim DSW1 As String ' Holds the Duty Schedule Week-1 sheet name
Dim DSW2 As String ' Holds the Duty Schedule Week-2 sheet name
Dim cg As Single ' This is a loop counter for collections crew Initials color (green) column AY on Duty Schedule sheets
Dim mr As Single ' This is a loop counter for maintenance crew Initials color (red) column AY on Duty Schedule sheets
Dim rb As Single ' This is a loop counter for repair crew Initials color column (blue) AY on Duty Schedule sheets
Dim uo As Single ' This is a loop counter for usa/services crew Initials color (orange) column AY on Duty Schedule sheets
Dim clm1 As String ' Holds the column name for left Friday entries
Dim clm2 As String ' Holds the column name for right Friday entries
DSW1 = "Duty Schedule Week-1"
DSW2 = "Duty Schedule Week-2"
' Call another macro to clear data before writing new data
' If Clear Data message box answer is no, exit both macros
Call ClearDutyScheduleWeek1and2
If iAnswer = vbNo Then Exit Sub
' Make sure 9-80 is the active worksheet
Worksheets("9-80").Activate
Sheets("9-80").Unprotect
Sheets("Duty Schedule Week-1").Unprotect
Sheets("Duty Schedule Week-2").Unprotect
' Find the last row used of Nine Eighty Employees
With Range("J2").CurrentRegion
Last980 = .Rows(.Rows.Count).Row
End With
' Find the last row used of 1st Friday
With Range("B3").CurrentRegion
Last1st = .Rows(.Rows.Count).Row
End With
' Find the last row used of 2nd Friday
With Range("E3").CurrentRegion
Last2nd = .Rows(.Rows.Count).Row
End With
' ######### This section is for the Duty Schedule Week-1 worksheet ############
' Get employee initials for the 1st Friday and place them on the Duty Schedule Week-1 worksheet
' in the proper crew column and Friday columns for 980 off
c = 3 ' Collections loop row number
m = 3 ' Maintenance Loop row number
r = 3 ' Repair Crew loop row number
u = 3 ' USAs/Services loop row number
fL = 3 ' Friday on the left loop row number
fR = 3 ' Friday on the right loop row number
fc1 = 20 ' Represents column T (20) or column V (22)
fc2 = 43 ' Represents column AQ (43) or column AS (45)
cg = 13 ' Collections green start row in column AY (51)
mr = 23 ' Maintenance red start row in column AY (51)
rb = 3 ' Reapair blue start row in column AY (51)
uo = 33 ' USAs orange start row in column AY (51)
clm2 = "AQ" ' Holds the column name of the first entry column for Friday on the right
clm1 = "T" ' Holds the column name of the first entry column for Friday on the left
'Stop
For n = 3 To Last1st
' On 9-80 sheet use vlookup to compare names in column B with column J and get initials from column K
[highlight #FCE94F]Emp = Application.VLookup(Range("B" & n), Range("J2:K25"), 2, False)[/highlight]
' This section grabs the initials of the names in column B of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column E of the
' 9-80 worksheet and puts them in the proper work group column.
' Check if Friday right side current cell is empty, if not increment counter
If Worksheets(DSW1).Range(clm2 & fR) <> "" Then fR = fR + 1
If fR > 10 Then fc2 = 45: If fR = 11 Then fR = 3 ' Switch columns if first column is full *********************************
If fc2 = 45 Then clm2 = "AS" ' Switch columns if first column is full
' Check if Friday left side current cell is empty, if not increment counter
If Worksheets(DSW1).Range(clm1 & fL) <> "" Then fL = fL + 1
If fL > 10 Then fc1 = 22: If fL = 11 Then fL = 3 ' Switch columns if first column is full *********************************
If fc1 = 22 Then clm1 = "V" ' Switch columns if first column is full
' Collections (column Y)
If Range("C" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW1).Cells(c, 25).Value = Emp: c = c + 1
If Range("C" & n).Value = "Collections" Then Worksheets(DSW1).Cells(cg, 51).Value = Emp: cg = cg + 1: _
Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = "980"
' Maintenance (column X)
If Range("C" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW1).Cells(m, 24).Value = Emp: m = m + 1
If Range("C" & n).Value = "Maintenance" Then Worksheets(DSW1).Cells(mr, 51).Value = Emp: mr = mr + 1: _
Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = 980
' Repair Crew (column A)
If Range("C" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW1).Cells(r, 1).Value = Emp: r = r + 1
If Range("C" & n).Value = "Repair Crew" Then Worksheets(DSW1).Cells(rb, 51).Value = Emp: rb = rb + 1: _
Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"
' USAs/Services (column B)
If Range("C" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW1).Cells(u, 2).Value = Emp: u = u + 1
If Range("C" & n).Value = "USAs/Services" Then Worksheets(DSW1).Cells(uo, 51).Value = Emp: uo = uo + 1: _
Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"
' Chief 1
If Range("C" & n).Value = "Chief 1" Then _
Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = "980"
' Chief 2
If Range("C" & n).Value = "Chief 2" Then _
Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"
Next
'Stop
' Get employee initials for the 2nd Friday of the pay period and place them on the
' Duty Schedule Week-1 worksheet in the proper crew column
For n = 3 To Last2nd
' On 9-80 sheet use vlookup to compare names in column E with column J and get initials from column K
Emp = Application.VLookup(Range("E" & n), Range("J2:K25"), 2, False)
' This section grabs the initials of the names in column B of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column E of the
' 9-80 worksheet and puts them in the proper work group column.
' Collections (column Y)
If Range("F" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW1).Cells(c, 25).Value = Emp: c = c + 1
If Range("F" & n).Value = "Collections" Then Worksheets(DSW1).Cells(cg, 51).Value = Emp: cg = cg + 1
' Maintenance (column X)
If Range("F" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW1).Cells(m, 24).Value = Emp: m = m + 1
If Range("F" & n).Value = "Maintenance" Then Worksheets(DSW1).Cells(mr, 51).Value = Emp: mr = mr + 1
' Repair Crew (column A)
If Range("F" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW1).Cells(r, 1).Value = Emp: r = r + 1
If Range("F" & n).Value = "Repair Crew" Then Worksheets(DSW1).Cells(rb, 51).Value = Emp: rb = rb + 1
' USAs/Services (column B)
If Range("F" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW1).Cells(u, 2).Value = Emp: u = u + 1
If Range("F" & n).Value = "USAs/Services" Then Worksheets(DSW1).Cells(uo, 51).Value = Emp: uo = uo + 1
Next
'Stop
' &&&&&&&&&&& This section is for the Duty Schedule Week-2 worksheet &&&&&&&&&&
' Get employee initials for the 2nd Friday and place them on the Duty Schedule Week-2 worksheet
' in the proper crew column and Friday columns for 980 off
c = 3 ' Collections loop row number
m = 3 ' Maintenance Loop row number
r = 3 ' Repair Crew loop row number
u = 3 ' USAs/Services loop row number
fL = 3 ' Friday on the left loop row number
fR = 3 ' Friday on the right loop row number
fc1 = 20 ' Represents column T (20) or column V (22)
fc2 = 43 ' Represents column AQ (43) or column AS (45)
cg = 13 ' Collections green start row in column AY (51)
mr = 23 ' Maintenance red start row in column AY (51)
rb = 3 ' Reapair blue start row in column AY (51)
uo = 33 ' USAs orange start row in column AY (51)
clm2 = "AQ" ' Holds the column name of the first entry column for Friday on the right
clm1 = "T" ' Holds the column name of the first entry column for Friday on the left
For n = 3 To Last2nd
' On 9-80 sheet use vlookup to compare names in column E with column J and get initials from column K
Emp = Application.WorksheetFunction.VLookup(Range("E" & n), Range("J2:K25"), 2, False)
' This section grabs the initials of the names in column E of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column B of the
' 9-80 worksheet and puts them in the proper work group column.
' Check if Friday right side current cell is empty, if not increment counter
If Worksheets(DSW2).Range(clm2 & fR) <> "" Then fR = fR + 1
If fR > 10 Then fc2 = 45: If fR = 11 Then fR = 3 ' Switch columns if first column is full *********************************
If fc2 = 45 Then clm2 = "AS" ' Switch columns if first column is full
' Check if Friday left side current cell is empty, if not increment counter
If Worksheets(DSW2).Range(clm1 & fL) <> "" Then fL = fL + 1
If fL > 10 Then fc1 = 22: If fL = 11 Then fL = 3 ' Switch columns if first column is full *********************************
If fc1 = 22 Then clm1 = "V" ' Switch columns if first column is full
' Collections (column Y)
If Range("F" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW2).Cells(c, 25).Value = Emp: c = c + 1
If Range("F" & n).Value = "Collections" Then Worksheets(DSW2).Cells(cg, 51).Value = Emp: cg = cg + 1: _
Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = "980"
' Maintenance (column X)
If Range("F" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW2).Cells(m, 24).Value = Emp: m = m + 1
If Range("F" & n).Value = "Maintenance" Then Worksheets(DSW2).Cells(mr, 51).Value = Emp: mr = mr + 1: _
Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = 980
' Repair Crew (column A)
If Range("F" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW2).Cells(r, 1).Value = Emp: r = r + 1
If Range("F" & n).Value = "Repair Crew" Then Worksheets(DSW2).Cells(rb, 51).Value = Emp: rb = rb + 1: _
Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"
' USAs/Services (column B)
If Range("F" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW2).Cells(u, 2).Value = Emp: u = u + 1
If Range("F" & n).Value = "USAs/Services" Then Worksheets(DSW2).Cells(uo, 51).Value = Emp: uo = uo + 1: _
Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"
' Chief 1
If Range("F" & n).Value = "Chief 1" Then _
Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = "980"
' Chief 2
If Range("F" & n).Value = "Chief 2" Then _
Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"
Next
' Get employee initials for the 2nd Friday of the pay period and place them on the
' Duty Schedule Week-1 worksheet in the proper crew column
For n = 3 To Last1st
' On 9-80 sheet use vlookup to compare names in column B with column J and get initials from column K
Emp = Application.WorksheetFunction.VLookup(Range("B" & n), Range("J2:K25"), 2, False)
' This section grabs the initials of the names in column E of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column B of the
' 9-80 worksheet and puts them in the proper work group column.
' Collections (column Y)
If Range("C" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW2).Cells(c, 25).Value = Emp: c = c + 1
If Range("C" & n).Value = "Collections" Then Worksheets(DSW2).Cells(cg, 51).Value = Emp: cg = cg + 1
' Maintenance (column X)
If Range("C" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW2).Cells(m, 24).Value = Emp: m = m + 1
If Range("C" & n).Value = "Maintenance" Then Worksheets(DSW2).Cells(mr, 51).Value = Emp: mr = mr + 1
' Repair Crew (column A)
If Range("C" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW2).Cells(r, 1).Value = Emp: r = r + 1
If Range("C" & n).Value = "Repair Crew" Then Worksheets(DSW2).Cells(rb, 51).Value = Emp: rb = rb + 1
' USAs/Services (column B)
If Range("C" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW2).Cells(u, 2).Value = Emp: u = u + 1
If Range("C" & n).Value = "USAs/Services" Then Worksheets(DSW2).Cells(uo, 51).Value = Emp: uo = uo + 1
Next
Sheets("9-80").Protect
Sheets("Duty Schedule Week-1").Protect
Sheets("Duty Schedule Week-2").Protect
End Sub