I have a code that currently works apart from where x is nothing....because for some reason it stills proceeds to attempt to paste x (which, I think, should only happen where If Not x Is Not Nothing)
Essentially if x is nothing I would like to proceed to the second part of the code (the bottom half) but without attempting to paste x to a new sheet- which should only happen when If Not x Is Not Nothing because otherwise it is pasting nothing and takes me to the debugger...
Apologies if I haven't explained that well...anyway here is the code:
Sub Copy_AboveBellow_Error()
Dim i As Long, x As Range
With Sheets("Input")
For i = 6 To .Range("g" & Rows.Count).End(xlUp).Row
If .Cells(i, "g").Value <> .Cells(i, "h").Value Then
If x Is Nothing Then
Set x = .Cells(i, "e").Resize(, 3)
Else
Set x = Union(x, .Cells(i, "e").Resize(, 3))
End If
End If
Next
End With
If Not x Is Nothing Then x.Copy
Sheets("Upload Sheet").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False
'second half
Dim bu As Boolean
With Sheets("Pre-Check")
For i = 6 To .Range("h" & Rows.Count).End(xlUp).Row
If .Cells(i, "h") = "Error - Not Inputted" Then
bu = True
Sheets("Discrepancies").Cells(Rows.Count, "e").End(xlUp)(2).Resize(, 3).Value = .Cells(i, "e").Resize(, 3).Value
End If
Next i
End With
If bu Then
MsgBox ("IMPORTANT (Discrepancies): there are some items from the pre-check that have a >0 count but that were not checked in your stock check...")
Sheets("Discrepancies").Activate
Else
UserForm1.Show
Sheets("Upload Sheet").Activate
End If
End Sub
Bookmarks