Option Explicit
Dim r As Integer, i As Integer
Dim convertedqty As Double, kiloconvert As Double, lookup2 As Double, sapstock As Double, v As String, rcount As Integer
Public vcount As Integer
Public oldStatusBar
Dim tcount As Integer, l As Integer, a As Variant
Sub checkinfo()
Dim lookup1 As Long, Rtrue As Long, Ptrue As Long, r As Integer
' counts how many rows exist (- headings)
r = Cells(Rows.Count, 1).End(xlUp).Row - 2
For i = 3 To r + 2
' these variables check for whether material is a raw (there is a corresponding "pack size" or a packaging item (anything else)
Rtrue = 0
Ptrue = 0
' determine the cell to get lookup value from
v = Cells(i, 3).Value
On Error Resume Next
' any value picked up from the following vlookup is a Raw Material
Rtrue = Application.WorksheetFunction.VLookup(v, Sheets("PackSize").Range("packsizes"), 2, False)
Err.Clear
Select Case Rtrue
Case Is > 0 ' if this is true the material is a Raw and diverts to sub to convert quantities
convertquantities ' run the sub to convert quantities
Case Else
' this code will show a value if the material is a packaging line
Ptrue = Application.WorksheetFunction.VLookup(v, Sheets("VigoStock").Range("AllVigoStock"), 7, False)
sapstock = Cells(i, 4)
lookup1 = 0 ' make sure variable is reset from previous loops
lookup1 = Application.VLookup(v, Range("AllVigoStock"), 7, False)
If lookup1 = sapstock Then
Cells(i, 5).Value = "Quantity Is Correct"
Cells(i, 6).Value = "" ' used to blank cells if re-running check
Cells(i, 7).Value = ""
Else
Cells(i, 5).Value = "Incorrect Quantity"
Cells(i, 6).Value = lookup1 - sapstock
Err.Clear
End If
End Select
Next i
End Sub
Sub convertquantities()
' all codes are "looked up" on SAP sheet and compared to:
On Error Resume Next
lookup2 = 0 ' make sure variable is reset from previous loops
' lookup = quantity on Vigo of given product
lookup2 = Application.VLookup(v, Range("AllVigoStock"), 7, False)
' kiloconvert = packsize of given product
kiloconvert = Application.VLookup(v, Range("packsizes"), 2, False)
convertedqty = lookup2 * kiloconvert
sapstock = Cells(i, 4)
If convertedqty = sapstock Then
Cells(i, 5).Value = "Quantity Is Correct"
Cells(i, 6).Value = ""
Cells(i, 7).Value = ""
Else
Cells(i, 5).Value = "Incorrect Quantity"
Cells(i, 6).Value = convertedqty - sapstock
End If
End Sub
Sub runall()
' run to check the row count and SAP quantity check at the same time
Dim q As Integer, msg As String, config As String, ans As Variant
' Application.ScreenUpdating = False
q = 1
Application.StatusBar = "Reconciling SAP Stock"
Sheets("SAP").Activate
checkinfo
Application.StatusBar = "Checking Vigo and SAP Row Counts"
Sheets("VigoStock").Activate
dorowsmatch
Application.StatusBar = "Highlighting Vigo Stock Gains"
highlightvigo
Application.StatusBar = "Highlighting SAP Losses"
Sheets("SAP").Activate
vigozero
Sheets("VigoStock").Activate
q = 0
msg = "SAP Raws and Packaging status checked"
msg = msg & vbNewLine & vbNewLine
msg = msg & "Variance on Vigo is " & tcount
msg = msg & vbNewLine
msg = msg & "(Highlighted if positive)"
config = vbOKOnly + vbInformation
ans = MsgBox(msg, config, "Check Complete")
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
' Application.ScreenUpdating = True
End Sub
Sub dorowsmatch()
Dim pcount As Integer
' count the rows in each column to confirm they match on first sheet (Vigo Raws)
' Application.ScreenUpdating = False
Sheets("VigoStock").Activate
vcount = Cells(Rows.Count, 1).End(xlUp).Row - 2 ' Vigo row count
Sheets("SAP").Activate
rcount = Cells(Rows.Count, 1).End(xlUp).Row - 2 ' SAP count
Sheets("VigoStock").Activate
Range("i2") = vcount ' Vigo total
Range("j2") = rcount ' SAP Total
tcount = vcount - (rcount + pcount) ' Variance
Range("k2") = tcount '
' Application.ScreenUpdating = True
End Sub
Sub ClearContents()
'
' Clear Cells Macro
'
Application.ScreenUpdating = False
Range("a3:g300").ClearContents
Range("linestatus").ClearContents
Application.ScreenUpdating = True
End Sub
Sub highlightvigo()
For i = 3 To vcount + 2
a = 0
a = Application.WorksheetFunction.CountIf(Range("SAP!C:C"), Cells(i, 1))
For l = 1 To 7
If a = 0 Then Cells(i, l).Select
With Selection.Font
.Bold = True
.Color = -11489280
.TintAndShade = 0
End With
Next l
Next i
Range("a3").Select
Selection.Font.Bold = False
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub
Sub vigozero()
Dim z As Variant, y As Variant, f As Double, h As Double
For f = 3 To rcount
z = Cells(f, 4).Value
y = Cells(f, 6).Value
For h = 1 To 4
If z + y = 0 Then Cells(f, h).Select
With Selection.Font
.Bold = True
.Color = -16776961
.TintAndShade = 0
End With
Next h
Next f
End Sub