How can I prevent this code from taking up too much memory? Would like to learn for this project and for future reference.
Sub Part5()
'Updating the classification columns all the way to the right
'Use a long if statement pulling from labels
Dim LastRow As Integer
Dim LastRowJira As Integer
Dim Temp As String
Dim row_index As String
Dim x As Integer
LastRow = Worksheets("Sheet1").Range("B" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
Lastrowpaste = LastRowJira 1
LastRowJira = Worksheets("Sheet0").Range("B" & Worksheets("Sheet0").Rows.Count).End(xlUp).Row
Dim AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO As String
Dim NN As String
NN = "Non-Nuclear"
Dim Nuc As String
Nuc = "Nuclear"
Dim PM As String
Dim MV As String
PM = "PostMVP"
MV = "MVP"
Dim SC As String
Dim WM As String
SC = "GEAM-Supply Chain"
WM = "GEAM-Work Mgmt"
Dim Conv As String
Conv = "Conversion"
Lookup = Worksheets("Sheet0").Range("$B$2:$B$" & LastRowJira)
WholeLookup = Worksheets("Sheet0").Range("$B$2:$GQ$" & LastRowJira)
On Error Resume Next
For x = 2 To LastRow
Temp = Worksheets("Sheet1").Range("B" & x).Value
row_index = Application.WorksheetFunction.Index(Lookup, WorksheetFunction.Match(Temp, Lookup, 0), 1)
AA = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 26)
AB = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 27)
AC = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 28)
AD = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 29)
AE = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 30)
AF = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 31)
AG = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 32)
AH = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 33)
AI = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 34)
AJ = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 35)
AK = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 36)
AL = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 37)
AM = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 38)
AN = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 39)
U = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 20)
Comp = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 21)
Sev = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 198)
Priority = Application.WorksheetFunction.Index(WholeLookup, WorksheetFunction.Match(Temp, Lookup, 0), 11)
'NN WM (NC S3) MVP , AR 44 -- Where we will paste if true
If U = "GEAM-Work Mgmt" And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AR" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AR" & x).Value = vbNullString
End If
'NN SCM (NC S3) MVP, AS 45
If U = "GEAM-Supply Chain" And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AS" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AS" & x).Value = vbNullString
End If
'NN All (NC S3) MVP, AT 46
If (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AT" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AT" & x).Value = vbNullString
End If
'NN WM (NC S3), AU 47
If U = "GEAM-Work Mgmt" And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AU" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AU" & x).Value = vbNullString
End If
'NN SCM (NC S3), AV 48
If U = SC And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AV" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AV" & x).Value = vbNullString
End If
'NN All (NC S3) All, AW 49
If (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AW" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AW" & x).Value = vbNullString
End If
'NUC WM (NC S3) MVP, AX 50
If U = WM And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AX" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AX" & x).Value = vbNullString
End If
'NUC SCM (NC S3) MVP, AY 51
If U = SC And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AY" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AY" & x).Value = vbNullString
End If
'NUC All (NC S3) MVP, AZ 52
If (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("AZ" & x).Value = row_index
Else
Worksheets("Sheet1").Range("AZ" & x).Value = vbNullString
End If
'NUC WM (NC S3), BA 53
If U = WM And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BA" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BA" & x).Value = vbNullString
End If
'NUC SCM (NC S3), BB 54
If U = SC And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BB" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BB" & x).Value = vbNullString
End If
'NUC All (NC S3), BC 55
If (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BC" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BC" & x).Value = vbNullString
End If
'MVP S3, BD 56
If (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BD" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BD" & x).Value = vbNullString
End If
'PostMVP S3, BE 57
If (AA = PM Or AB = PM Or AC = PM Or AD = PM Or AE = PM Or AF = PM Or AG = PM Or AH = PM Or AI = PM Or AJ = PM Or AK = PM Or AL = PM Or AM = PM Or AN = PM Or AO = PM) And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BE" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BE" & x).Value = vbNullString
End If
'NN Conv WM S3 MVP, BF 58
If U = WM And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BF" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BF" & x).Value = vbNullString
End If
'NN Conv SCM S3 MVP, BG 59
If U = SC And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BG" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BG" & x).Value = vbNullString
End If
'NN Conv All S3 MVP, BH 60
If (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BH" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BH" & x).Value = vbNullString
End If
'NN Conv WM S3, BI 61
If U = WM And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BI" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BI" & x).Value = vbNullString
End If
'NN Conv SCM S3, BJ 62
If U = SC And (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BJ" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BJ" & x).Value = vbNullString
End If
'NN Conv All S3, BL 63
If (AA = NN Or AB = NN Or AC = NN Or AD = NN Or AE = NN Or AF = NN Or AG = NN Or AH = NN Or AI = NN Or AJ = NN Or AK = NN Or AL = NN Or AM = NN Or AN = NN Or AO = NN) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BL" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BL" & x).Value = vbNullString
End If
'Nuc Conv SCM S3 MVP, BM 64
If U = SC And (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BM" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BM" & x).Value = vbNullString
End If
'Nuc Conv All S3 MVP, BN 65
If (AA = "MVP" Or AB = "MVP" Or AC = "MVP" Or AD = "MVP" Or AE = "MVP" Or AF = "MVP" Or AG = "MVP" Or AH = "MVP" Or AI = "MVP" Or AJ = "MVP" Or AK = "MVP" Or AL = "MVP" Or AM = "MVP" Or AN = "MVP" Or AO = "MVP") And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BN" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BN" & x).Value = vbNullString
End If
'Nuc Conv WM S3, BO 66
If U = WM And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BO" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BO" & x).Value = vbNullString
End If
'Nuc Conv SCM S3, BP 67
If U = SC And (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BP" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BP" & x).Value = vbNullString
End If
'Nuc Conv All S3, BQ 68
If (AA = Nuc Or AB = Nuc Or AC = Nuc Or AD = Nuc Or AE = Nuc Or AF = Nuc Or AG = Nuc Or AH = Nuc Or AI = Nuc Or AJ = Nuc Or AK = Nuc Or AL = Nuc Or AM = Nuc Or AN = Nuc Or AO = Nuc) And Comp = Conv And (Sev = "SEV 2" Or (Sev = "SEV 3" And Priority = "High") Or (Sev = "SEV 3" And Priority = "Highest")) Then
Worksheets("Sheet1").Range("BQ" & x).Value = row_index
Else
Worksheets("Sheet1").Range("BQ" & x).Value = vbNullString
End If
Next x
End Sub
I was considering a nested for loop? I tried separating loops for each column? I am a novice if you could not tell by the code.
CodePudding user response：
I will guess: If the issue is memory then instead of creating arrays for Lookup and WholeLookup, try using the Range.
Replace this:
Lookup = Worksheets("Sheet0").Range("$B$2:$B$" & LastRowJira)
WholeLookup = Worksheets("Sheet0").Range("$B$2:$GQ$" & LastRowJira)
With this:
Set Lookup = Worksheets("Sheet0").Range("$B$2:$B$" & LastRowJira)
Set WholeLookup = Worksheets("Sheet0").Range("$B$2:$GQ$" & LastRowJira)