Supersipe
05-25-2022, 02:37 AM
Alcon,
BLUF:
I am requesting help on the below code. The key things I am requesting help:
1. Speed it up. It is OMG slow. (at least 20-30 minutes and not all the way finished)
2. I believe the use of Dictionary/Array's like Ubound would work way quicker.
3. General cleanup of the code (if need be).
Background:
I have written over the past month or so. Yes, I'm a noob. I have come to the forums, YouTube and what you see is what I have taught myself over the past month or so. But I'm stuck. In my mind, some of it is complex because for my use in the Army, I am pulling Civilians, local nationals and all ranks within the Army. The issue is that I am pulling from 4 different data sheets (DTMS Roster, DTMS Training, DTMS ACFT, DTMS Weapons). Within those 4 sheets, I have to extract the data and pull it to Master Data sheet. I KNOW there is a quicker way, but I am truly at a loss now and don't know where to turn. Any help would be GREATLY appreciated.
here is the code
' MasterData Macro
'
'DON'T FORGET TO EXEMPT THE RANK/MOST FROM ICTL'S
Application.ScreenUpdating = False
Application.EnableEvents = False
MsgBox ("Hang tight. Your data is now being compiled.")
Dim lRow As Integer 'variable for last row of column
Dim lRowU As Integer
Dim lRowT As Integer
Dim lRowR As Integer
Dim weplRow As Integer
Dim edipi As String
Dim rankNo As Integer
Dim ictlCount As Integer
Dim found As Integer
Dim rg As Range
Dim shMaster As Worksheet
Dim shRoster As Worksheet
Dim shTraining As Worksheet
Dim shWeapons As Worksheet
Dim shICTL As Worksheet
Dim shConversion As Worksheet
'Pull DTMS Roster Rank No, Main Unit, and Last, First
lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
Set shMaster = Worksheets("Master Data")
Set shRoster = Worksheets("DTMS Roster")
Set shTraining = Worksheets("DTMS Training")
Set shWeapons = Worksheets("DTMS Weapons")
Set shICTL = Worksheets("ICTL Data Table")
Set shConversion = Worksheets("Conversion Files")
'Clear any Data
shMaster.Range("A1").CurrentRegion.Offset(1).ClearContents
'Last row but also vlookups and conversion to values in Roster to paste over to Master Data
Worksheets("DTMS Roster").Select
Range("A1").Select
'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Clear
'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Add Key:= _
'Range("H1:H670"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
':=xlSortNormal
'With ActiveWorkbook.Worksheets("DTMS Roster").AutoFilter.Sort
' .Header = xlYes
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
'End With
shRoster.Range("G1").Value2 = "Rank No"
Range("H1").Value2 = "Main Unit"
Range("I1").Value2 = "Last, First"
Range("G2").Value2 = "=IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")"
Range("H2").Value2 = "=IFERROR(VLOOKUP(D2,'Conversion Files'!A:B,2,FALSE),"""")"
Range("I2").Value2 = "=PROPER(B2)"
Range("G2:I2").Select
Selection.AutoFill Destination:=Range("G2:I" & lRowR)
Columns("G:I").Copy
Columns("G:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
'Pull DTMS Roster Rank No, Main Unit, and Last, First
lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
'DTMS Training vlookup for last 3 columms (V, W, X) and convert to value to ensure lRow is completed. this _
avoids extra cells in Master Data
shTraining.Select
Range("V2").Value2 = "Skill Level"
Range("W2").Value2 = "Loop Numerator" 'this is the MOS finder associated with skill level
Range("X2").Value2 = "UnitEasy"
Range("V2").Value2 = "=IFERROR(VLOOKUP(RC[-4],'ICTL Data Table'!C[-20]:C[-19],2,FALSE),"""")"
Range("W2").Value2 = "=IFERROR(VLOOKUP(C[-5],'ICTL Data Table'!C[-21]:C[-19],3,FALSE),"""")"
Range("X2").Value2 = "=IFERROR(VLOOKUP(C[-12],'Conversion Files'!C[-23]:C[-22],2,FALSE),"""")"
Range("V2:X2").Select
Selection.AutoFill Destination:=Range("V2:X2" & lRowT)
Columns("V:X").Copy
Columns("V:X").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
'Select Master Data sheet to begin copying all data and make magic happen
Worksheets("Master Data").Select
Range("A2").Select
' AdvFilter
Set shRoster = Worksheets("DTMS Roster")
'Super Copy Code
Set rg = shRoster.Range("A1").CurrentRegion
'EPIDI
Set criteriaRange = Worksheets("AdvFilter").Range("A1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("A1")
'Last, First
Set criteriaRange = Worksheets("AdvFilter").Range("C1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("B1")
'Rank
Set criteriaRange = Worksheets("AdvFilter").Range("E1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("C1")
'MOS
Set criteriaRange = Worksheets("AdvFilter").Range("G1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("D1")
'Unit
Set criteriaRange = Worksheets("AdvFilter").Range("I1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("E1")
lRow = Worksheets("Master Data").Range("B1").End(xlDown).Row
'With Columns A:Z
With Columns("A:A") 'EDIPI Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 11
With .Font
.Name = "Arial"
.Size = 10
.Bold = False
End With
End With
With Columns("B:B") 'Last Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 30
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("C:D") 'Rank/MOS Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 6
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("E:E") 'Main Unit Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 13
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("F:F") 'ACFT Date Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("G:H") 'ACFT Score/Weapon Type Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 8
.NumberFormat = "0"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
With Columns("I:I") 'Weapon Date Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
With Columns("J:K") 'ICT Completed/Assigned Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 9
.NumberFormat = "0"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
lRow = Range("B1").End(xlDown).Row
With Columns("M:Z") '350-1 Training (Blue/Orange Training)
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
'With Ranges (Headers A:Z)
With Range("A1:E1") 'EDIPI Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("F1:L1") 'ACFT Date thru ICT % Alignment
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
With Range("F1")
.Value2 = "ACFT Date"
End With
With Range("G1") 'ACFT Score Header
.Value2 = "ACFT Score"
End With
With Range("H1") 'Weapon Type Header
.Value2 = "Weapon Type"
End With
With Range("I1") 'Weapon Date Header
.Value2 = "Weapon Date"
End With
With Range("J1") 'ICT Completed Headerw
.Value2 = "ICT Completed"
End With
With Range("K1") 'ICT Assigned Header
.Value2 = "ICT Assigned"
End With
With Range("L1") 'ICT % Complete Header
.Value2 = "ICT % Complete"
End With
With Range("M1") 'EO Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Value2 = "EO"
End With
With Range("N1") 'Resilience Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Value2 = "Resilience"
End With
With Range("O1") 'Personal Readiness Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Value2 = "Personal Readiness"
End With
With Range("P1:S1") 'INFOSEC Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("P1")
.Value2 = "INFOSEC"
End With
With Range("Q1") 'OPSEC Header
.Value2 = "OPSEC"
End With
With Range("R1") 'SHARP Header
.Value2 = "SHARP"
End With
With Range("S1") 'AT Lvl 1 Header
.Value2 = "AT Lvl 1"
End With
With Range("T1") 'Cyber Awareness Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Value2 = "Cyber Awareness"
End With
With Range("U1:V1") 'HIPAA Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("U1")
.Value2 = "HIPAA"
End With
With Range("V1") 'TARP Header
.Value2 = "TARP"
End With
With Range("W1:Z1") 'Maintin M4 AWT 1 Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
With Range("W1")
.Value2 = "Maintin M4 AWT 1"
End With
With Range("X1") 'Under Fire AWT 2 Header
.Value2 = "Under Fire AWT 2"
End With
With Range("Y1") 'TC3 Card AWT 3 Header
.Value2 = "TC3 Card AWT 3"
End With
With Range("Z1") 'SITREP AWT 4 Header
.Value2 = "SITREP AWT 4"
End With
'We MAY NOT need this vlookup at all. simply add the exempt to the Cases! saves on a .Select
'Pull ACFT Date and Score
'Range("F2").Select
'Range("F2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",(VLOOKUP(A2,'DTMS ACFT'!C:K,9,FALSE)))),"""")"
'Range("G2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",VLOOKUP(A2,'DTMS ACFT'!C:L,10,FALSE))),"""")"
'Range("G2:F2").Select
'Selection.AutoFill Destination:=Range("F2:G" & lRow)
'Columns("F:G").Copy
'Columns("F:G").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
'Application.CutCopyMode = False
'Pull Weapon Type
Range("H2").Value2 = "=IF(IFERROR(VLOOKUP(B2,'Conversion Files'!S:U,3,FALSE),"""")<>""Y"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<5,""M4"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<30,""M17"",""Exempt"")),""IMA"")"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lRow)
Columns("H:H").Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Pull Weapon Date
weplRow = shWeapons.Range("A1").End(xlDown).Row
For i = 2 To lRow
'M17 qual lookup
If Left(shMaster.Range("C" & i).Value2, 1) = "G" Then shMaster.Range("I" & i).Value2 = "Exempt" 'IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",
If shMaster.Range("C" & i).Value2 = "CON" Then shMaster.Range("I" & i).Value2 = "Exempt"
If shMaster.Range("H" & i).Value2 = "M17" Then 'if masterDatatab Gi = "M17" then
'edipi = Worksheets("Master Data").Range("A" & i).Value 'EDIPI = Ai
For j = 2 To weplRow
If shWeapons.Range("E" & j).Value2 = shMaster.Range("A" & i).Value2 Then 'if Weaponstap Ej = EDIPI Then
For k = j To j + 10
If shWeapons.Range("E" & k).Value2 <> edipi Then
k = k + 10
End If
If shWeapons.Range("P" & k).Value2 = "XM-17 PISTOL, MODULAR" Or shWeapons.Range("P" & k).Value2 = "M9 9mm Beretta Pistol" Then 'if Pk = "M17" or "M9" then
shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value 'MasterDataTab Hi.value = Weaponstab Tk.value
k = k + 10
End If
Next k
j = j + weplRow
End If
Next j
'else if shMaster.Range("H" & i).Value = "IMA" Then
End If
'M4 qual lookup
If shMaster.Range("H" & i).Value2 = "M4" Then 'if masterDatatab Gi = "M4" then
edipi = shMaster.Range("A" & i).Value2 'EDIPI = Ai
For j = 2 To weplRow
If shWeapons.Range("E" & j).Value2 = edipi Then 'if Weaponstap Ej = EDIPI Then
For k = j To j + 10
If shWeapons.Range("E" & k).Value2 <> edipi Then
k = k + 10
End If
If Left(Worksheets("DTMS Weapons").Range("P" & k).Value2, 2) = "M4" Then 'if Pk = "M4" then
shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value2 'MasterDataTab Hi.value2 = Weaponstab Tk.value2
k = k + 10
End If
Next k
j = j + weplRow
End If
Next j
End If
Next i
'ICTL Data
ictlCount = 0
For i = 2 To lRow
For j = 2 To 42
If shMaster.Range("C" & i).Value2 = shConversion.Range("H" & j).Value2 Then
rankNo = shConversion.Range("I" & j).Value2
j = j + 42
End If
Next j
'For k = 2 To 31
'If shMaster.Range("D" & i).Value = shICTL.Range("AK" & k).Value Then
Select Case rankNo
Case 1, 11, 12, 13, 14, 15
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AL" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator and to check for training dates
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 = 1 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 2
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AM" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 < 3 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 3
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AN" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 <= 3 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 4, 5
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AO" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case Worksheets("DTMS Training").Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
ictlCount = ictlCount + 1
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 6, 16, 20
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
'79S and 91M are also exempt. add to code somehow to exempt those MOS? or any other MOS? that is excluded?
'i have the list.
'maybe assign execmpt MOS a case rankno like 61=79S 62=91M etc?
'If Worksheets("Master Data").Range("C").Value = "79S" Then
' Worksheets("Master Data").Range("J" & i).Value = "Exempt"
'End If
'66H has 3 spaces like this "66H " for how it is from dtms
'68C has 2 spaces liek this "68C " for how it is from dtms
Case 30
For l = 2 To lRowT 'start looking through the Training data
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case 40 'CON. 6 Total Trainings Required
For l = 2 To lRowT 'start looking through the Training data
If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("R" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case 50 'Local Nationals. 5 Total Trainings Required SHARP, AT Lvl 1, HIPAA, TARP
For l = 2 To lRowT 'start looking through the Training data
If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("O" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("P" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case Else
shMaster.Range("J" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("K" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("L" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("M" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("N" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("O" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("P" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("Q" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("R" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("S" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("T" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("U" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("V" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
End Select
Next i
With Columns("L:L") 'ICT % Complete Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 9
.NumberFormat = "0%"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-2]/RC[-1],"""")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lRow)
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
Worksheets("Master Data").Select
shMaster.Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
'Dim DataObj As New MSForms.DataObject 'empty the clipboard
'DataObj.SetText ""
'DataObj.PutInClipboard
End Sub
BLUF:
I am requesting help on the below code. The key things I am requesting help:
1. Speed it up. It is OMG slow. (at least 20-30 minutes and not all the way finished)
2. I believe the use of Dictionary/Array's like Ubound would work way quicker.
3. General cleanup of the code (if need be).
Background:
I have written over the past month or so. Yes, I'm a noob. I have come to the forums, YouTube and what you see is what I have taught myself over the past month or so. But I'm stuck. In my mind, some of it is complex because for my use in the Army, I am pulling Civilians, local nationals and all ranks within the Army. The issue is that I am pulling from 4 different data sheets (DTMS Roster, DTMS Training, DTMS ACFT, DTMS Weapons). Within those 4 sheets, I have to extract the data and pull it to Master Data sheet. I KNOW there is a quicker way, but I am truly at a loss now and don't know where to turn. Any help would be GREATLY appreciated.
here is the code
' MasterData Macro
'
'DON'T FORGET TO EXEMPT THE RANK/MOST FROM ICTL'S
Application.ScreenUpdating = False
Application.EnableEvents = False
MsgBox ("Hang tight. Your data is now being compiled.")
Dim lRow As Integer 'variable for last row of column
Dim lRowU As Integer
Dim lRowT As Integer
Dim lRowR As Integer
Dim weplRow As Integer
Dim edipi As String
Dim rankNo As Integer
Dim ictlCount As Integer
Dim found As Integer
Dim rg As Range
Dim shMaster As Worksheet
Dim shRoster As Worksheet
Dim shTraining As Worksheet
Dim shWeapons As Worksheet
Dim shICTL As Worksheet
Dim shConversion As Worksheet
'Pull DTMS Roster Rank No, Main Unit, and Last, First
lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
Set shMaster = Worksheets("Master Data")
Set shRoster = Worksheets("DTMS Roster")
Set shTraining = Worksheets("DTMS Training")
Set shWeapons = Worksheets("DTMS Weapons")
Set shICTL = Worksheets("ICTL Data Table")
Set shConversion = Worksheets("Conversion Files")
'Clear any Data
shMaster.Range("A1").CurrentRegion.Offset(1).ClearContents
'Last row but also vlookups and conversion to values in Roster to paste over to Master Data
Worksheets("DTMS Roster").Select
Range("A1").Select
'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Clear
'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Add Key:= _
'Range("H1:H670"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
':=xlSortNormal
'With ActiveWorkbook.Worksheets("DTMS Roster").AutoFilter.Sort
' .Header = xlYes
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
'End With
shRoster.Range("G1").Value2 = "Rank No"
Range("H1").Value2 = "Main Unit"
Range("I1").Value2 = "Last, First"
Range("G2").Value2 = "=IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")"
Range("H2").Value2 = "=IFERROR(VLOOKUP(D2,'Conversion Files'!A:B,2,FALSE),"""")"
Range("I2").Value2 = "=PROPER(B2)"
Range("G2:I2").Select
Selection.AutoFill Destination:=Range("G2:I" & lRowR)
Columns("G:I").Copy
Columns("G:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
'Pull DTMS Roster Rank No, Main Unit, and Last, First
lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
'DTMS Training vlookup for last 3 columms (V, W, X) and convert to value to ensure lRow is completed. this _
avoids extra cells in Master Data
shTraining.Select
Range("V2").Value2 = "Skill Level"
Range("W2").Value2 = "Loop Numerator" 'this is the MOS finder associated with skill level
Range("X2").Value2 = "UnitEasy"
Range("V2").Value2 = "=IFERROR(VLOOKUP(RC[-4],'ICTL Data Table'!C[-20]:C[-19],2,FALSE),"""")"
Range("W2").Value2 = "=IFERROR(VLOOKUP(C[-5],'ICTL Data Table'!C[-21]:C[-19],3,FALSE),"""")"
Range("X2").Value2 = "=IFERROR(VLOOKUP(C[-12],'Conversion Files'!C[-23]:C[-22],2,FALSE),"""")"
Range("V2:X2").Select
Selection.AutoFill Destination:=Range("V2:X2" & lRowT)
Columns("V:X").Copy
Columns("V:X").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
'Select Master Data sheet to begin copying all data and make magic happen
Worksheets("Master Data").Select
Range("A2").Select
' AdvFilter
Set shRoster = Worksheets("DTMS Roster")
'Super Copy Code
Set rg = shRoster.Range("A1").CurrentRegion
'EPIDI
Set criteriaRange = Worksheets("AdvFilter").Range("A1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("A1")
'Last, First
Set criteriaRange = Worksheets("AdvFilter").Range("C1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("B1")
'Rank
Set criteriaRange = Worksheets("AdvFilter").Range("E1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("C1")
'MOS
Set criteriaRange = Worksheets("AdvFilter").Range("G1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("D1")
'Unit
Set criteriaRange = Worksheets("AdvFilter").Range("I1").CurrentRegion
rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("E1")
lRow = Worksheets("Master Data").Range("B1").End(xlDown).Row
'With Columns A:Z
With Columns("A:A") 'EDIPI Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 11
With .Font
.Name = "Arial"
.Size = 10
.Bold = False
End With
End With
With Columns("B:B") 'Last Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 30
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("C:D") 'Rank/MOS Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 6
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("E:E") 'Main Unit Column
.HorizontalAlignment = xlLeft
.ColumnWidth = 13
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("F:F") 'ACFT Date Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "Arial"
.Size = 10
.Bold = True
End With
End With
With Columns("G:H") 'ACFT Score/Weapon Type Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 8
.NumberFormat = "0"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
With Columns("I:I") 'Weapon Date Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
With Columns("J:K") 'ICT Completed/Assigned Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 9
.NumberFormat = "0"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
lRow = Range("B1").End(xlDown).Row
With Columns("M:Z") '350-1 Training (Blue/Orange Training)
.HorizontalAlignment = xlCenter
.ColumnWidth = 12
.NumberFormat = "dd mmm yyyy"
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
'With Ranges (Headers A:Z)
With Range("A1:E1") 'EDIPI Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("F1:L1") 'ACFT Date thru ICT % Alignment
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
With Range("F1")
.Value2 = "ACFT Date"
End With
With Range("G1") 'ACFT Score Header
.Value2 = "ACFT Score"
End With
With Range("H1") 'Weapon Type Header
.Value2 = "Weapon Type"
End With
With Range("I1") 'Weapon Date Header
.Value2 = "Weapon Date"
End With
With Range("J1") 'ICT Completed Headerw
.Value2 = "ICT Completed"
End With
With Range("K1") 'ICT Assigned Header
.Value2 = "ICT Assigned"
End With
With Range("L1") 'ICT % Complete Header
.Value2 = "ICT % Complete"
End With
With Range("M1") 'EO Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Value2 = "EO"
End With
With Range("N1") 'Resilience Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Value2 = "Resilience"
End With
With Range("O1") 'Personal Readiness Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Value2 = "Personal Readiness"
End With
With Range("P1:S1") 'INFOSEC Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("P1")
.Value2 = "INFOSEC"
End With
With Range("Q1") 'OPSEC Header
.Value2 = "OPSEC"
End With
With Range("R1") 'SHARP Header
.Value2 = "SHARP"
End With
With Range("S1") 'AT Lvl 1 Header
.Value2 = "AT Lvl 1"
End With
With Range("T1") 'Cyber Awareness Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Value2 = "Cyber Awareness"
End With
With Range("U1:V1") 'HIPAA Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
With Range("U1")
.Value2 = "HIPAA"
End With
With Range("V1") 'TARP Header
.Value2 = "TARP"
End With
With Range("W1:Z1") 'Maintin M4 AWT 1 Header
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
With Range("W1")
.Value2 = "Maintin M4 AWT 1"
End With
With Range("X1") 'Under Fire AWT 2 Header
.Value2 = "Under Fire AWT 2"
End With
With Range("Y1") 'TC3 Card AWT 3 Header
.Value2 = "TC3 Card AWT 3"
End With
With Range("Z1") 'SITREP AWT 4 Header
.Value2 = "SITREP AWT 4"
End With
'We MAY NOT need this vlookup at all. simply add the exempt to the Cases! saves on a .Select
'Pull ACFT Date and Score
'Range("F2").Select
'Range("F2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",(VLOOKUP(A2,'DTMS ACFT'!C:K,9,FALSE)))),"""")"
'Range("G2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",VLOOKUP(A2,'DTMS ACFT'!C:L,10,FALSE))),"""")"
'Range("G2:F2").Select
'Selection.AutoFill Destination:=Range("F2:G" & lRow)
'Columns("F:G").Copy
'Columns("F:G").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
'Application.CutCopyMode = False
'Pull Weapon Type
Range("H2").Value2 = "=IF(IFERROR(VLOOKUP(B2,'Conversion Files'!S:U,3,FALSE),"""")<>""Y"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<5,""M4"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<30,""M17"",""Exempt"")),""IMA"")"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lRow)
Columns("H:H").Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Pull Weapon Date
weplRow = shWeapons.Range("A1").End(xlDown).Row
For i = 2 To lRow
'M17 qual lookup
If Left(shMaster.Range("C" & i).Value2, 1) = "G" Then shMaster.Range("I" & i).Value2 = "Exempt" 'IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",
If shMaster.Range("C" & i).Value2 = "CON" Then shMaster.Range("I" & i).Value2 = "Exempt"
If shMaster.Range("H" & i).Value2 = "M17" Then 'if masterDatatab Gi = "M17" then
'edipi = Worksheets("Master Data").Range("A" & i).Value 'EDIPI = Ai
For j = 2 To weplRow
If shWeapons.Range("E" & j).Value2 = shMaster.Range("A" & i).Value2 Then 'if Weaponstap Ej = EDIPI Then
For k = j To j + 10
If shWeapons.Range("E" & k).Value2 <> edipi Then
k = k + 10
End If
If shWeapons.Range("P" & k).Value2 = "XM-17 PISTOL, MODULAR" Or shWeapons.Range("P" & k).Value2 = "M9 9mm Beretta Pistol" Then 'if Pk = "M17" or "M9" then
shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value 'MasterDataTab Hi.value = Weaponstab Tk.value
k = k + 10
End If
Next k
j = j + weplRow
End If
Next j
'else if shMaster.Range("H" & i).Value = "IMA" Then
End If
'M4 qual lookup
If shMaster.Range("H" & i).Value2 = "M4" Then 'if masterDatatab Gi = "M4" then
edipi = shMaster.Range("A" & i).Value2 'EDIPI = Ai
For j = 2 To weplRow
If shWeapons.Range("E" & j).Value2 = edipi Then 'if Weaponstap Ej = EDIPI Then
For k = j To j + 10
If shWeapons.Range("E" & k).Value2 <> edipi Then
k = k + 10
End If
If Left(Worksheets("DTMS Weapons").Range("P" & k).Value2, 2) = "M4" Then 'if Pk = "M4" then
shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value2 'MasterDataTab Hi.value2 = Weaponstab Tk.value2
k = k + 10
End If
Next k
j = j + weplRow
End If
Next j
End If
Next i
'ICTL Data
ictlCount = 0
For i = 2 To lRow
For j = 2 To 42
If shMaster.Range("C" & i).Value2 = shConversion.Range("H" & j).Value2 Then
rankNo = shConversion.Range("I" & j).Value2
j = j + 42
End If
Next j
'For k = 2 To 31
'If shMaster.Range("D" & i).Value = shICTL.Range("AK" & k).Value Then
Select Case rankNo
Case 1, 11, 12, 13, 14, 15
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AL" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator and to check for training dates
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 = 1 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 2
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AM" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 < 3 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 3
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AN" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
If shTraining.Range("V" & l).Value2 <= 3 Then
ictlCount = ictlCount + 1
End If
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 4, 5
For k = 2 To 31
If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
shMaster.Range("K" & i).Value2 = shICTL.Range("AO" & k).Value2 'denominator
k = 31
Else
If k = 31 Then
shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
End If
End If
Next k
For l = 2 To lRowT 'loop to count the numerator
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
found = 1
Select Case Worksheets("DTMS Training").Range("R" & l).Value2
Case "DA-CMT12" 'Resilience
shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT10" 'EO / EEO
shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
ictlCount = ictlCount + 1
End If
Else
If found = 1 Then
l = lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
Else
shMaster.Range("J" & i).Value2 = ictlCount 'numerator
ictlCount = 0
End If
Case 6, 16, 20
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
'79S and 91M are also exempt. add to code somehow to exempt those MOS? or any other MOS? that is excluded?
'i have the list.
'maybe assign execmpt MOS a case rankno like 61=79S 62=91M etc?
'If Worksheets("Master Data").Range("C").Value = "79S" Then
' Worksheets("Master Data").Range("J" & i).Value = "Exempt"
'End If
'66H has 3 spaces like this "66H " for how it is from dtms
'68C has 2 spaces liek this "68C " for how it is from dtms
Case 30
For l = 2 To lRowT 'start looking through the Training data
If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT15" 'Personal Readiness
shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case 40 'CON. 6 Total Trainings Required
For l = 2 To lRowT 'start looking through the Training data
If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "DA-CMT18" 'INFOSEC
shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT16" 'OPSEC
shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("R" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case 50 'Local Nationals. 5 Total Trainings Required SHARP, AT Lvl 1, HIPAA, TARP
For l = 2 To lRowT 'start looking through the Training data
If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
found = 1
Select Case shTraining.Range("R" & l).Value2
Case "150S-SHA-0100" 'SHARP
shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT01" 'AT Lvl 1
shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT19" 'Cyber Awareness
shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
Case "MC - 00020" 'HIPAA
shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
Case "DA-CMT02" 'TARP
shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
End Select
shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("O" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
shMaster.Range("P" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
Else
If found = 1 Then
l = l + lRowT
found = 0
Else
If l = lRowT Then
shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
End If
End If
End If
Next l
Case Else
shMaster.Range("J" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("K" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("L" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("M" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("N" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("O" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("P" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("Q" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("R" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("S" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("T" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("U" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
shMaster.Range("V" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
End Select
Next i
With Columns("L:L") 'ICT % Complete Column
.HorizontalAlignment = xlCenter
.ColumnWidth = 9
.NumberFormat = "0%"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-2]/RC[-1],"""")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lRow)
With .Font
.Name = "arial"
.Size = 10
.Bold = True
End With
End With
Worksheets("Master Data").Select
shMaster.Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
'Dim DataObj As New MSForms.DataObject 'empty the clipboard
'DataObj.SetText ""
'DataObj.PutInClipboard
End Sub