I have some code I cobbled together which is working just great and I am using the dictionary with some code I believe Fluff wrote.
Can anybody please tell me what the offset is for in this line
If I remove it and put
The code does not work
Full working code below
Can anybody please tell me what the offset is for in this line
VBA Code:
Dic(Cl.Value) = Array(Cl, Cl.Offset(, 1).Value)
VBA Code:
Dic(Cl.Value) = Array(Cl, Cl.Value)
Full working code below
VBA Code:
Sub CheckIfDispatched()
Dim WB1 As Workbook
Dim WB2 As Workbook
Application.ScreenUpdating = False
' Capture current workbook
Set WB1 = ActiveWorkbook
'***************************************
Dim a As Integer 'Clearing old data from sheet 2 of the cert pack tracker workbook
a = Sheets("Sheet2").Range("E2:E" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("E2" & a).ClearContents
'***************************************
' Opening the Open SO Items register
Workbooks.Open Filename:="L:\EMAX\EMAX REPORTS\Open SO Items.xlsx", ReadOnly:=True
' Capture new workbook
Set WB2 = ActiveWorkbook
Sheets("Open SO Items").Select
If Sheets("Open SO Items").FilterMode Then ActiveSheet.ShowAllData 'Clear All Filters for entire Table
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
'***************************************
'Copying the SO numbers
a = Sheets("Open SO Items").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Open SO Items").Range("A2:A" & a).Copy
'***************************************
' Go back to original workbook
WB1.Activate
'Pasting the SO numbers
Sheets("Sheet2").Range("E2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
'**************************************
'comparing SO numbers, to see if the SO number is still on the open SO items register, otherwise it may have been cancelled or dispatched
Dim Cl As Range
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2") 'adding all the SO numbers from the pasted values on sheet 2 from the open so items register to the Dic (dictionary)
For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Array(Cl, Cl.Offset(, 1).Value)
Next Cl
End With
With Sheets("Sheet1")
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp)) 'comparing the SO numbers from sheet1 to the stored numbers in the Dic (Dictionary)
If Not Dic.Exists(Cl.Value) Then ' if it does not appear in the Dic (dictionary) then highlight green
Cl.Offset(0, 1).Interior.Color = vbGreen
End If
Next Cl
End With
WB2.Close False ' closing the Open SO items register
Application.ScreenUpdating = True
End Sub