I currently have a macro that validates one column Vs. another column and extrapulates all matched data.
I now want to reverse this and only find the all the part numbers that are not matches. i know this is probably easy, but i just can't play around enough with the code for it to work. Any ideas?
[VBA]Sub Button1_Click()
UserForm1.Label1.Width = 0
UserForm1.Show
End Sub
Sub Macro1()
'
' Macro1 Macro
'
Application.ScreenUpdating = False
Sheets("Input").Select
Range("A2").Select
If ActiveCell.Value <> "" Then
If ActiveCell.Offset(0, 2) <> "" And ActiveCell.Offset(0, 5) <> "" And ActiveCell.Offset(0, 6) <> "" Then
Sheets("BlackBox").Select
Range("A2").Select
Dim ArrBB(300000) As String
Dim BBCount As Long
'populate array with values
For BBCount = 2 To ActiveSheet.UsedRange.Rows.count + 1
If ActiveCell.Value = "" Then
Exit For
End If
Range("A" & BBCount).Select
ArrBB(BBCount) = ActiveCell.Value
Next BBCount
'loop through input and check each against bb array
Dim InputCount As Long
Dim Loopcount As Long
Dim place As Long
Sheets("Input").Select
place = 2
For InputCount = 2 To ActiveSheet.UsedRange.Rows.count
Range("A" & InputCount).Select
For Loopcount = 1 To BBCount
If ActiveCell.Value = ArrBB(Loopcount) And ActiveCell.Value <> "" Then
Worksheets("Output").Range("A" & place) = ActiveCell.Offset(0, 0).Value
Worksheets("Output").Range("B" & place) = ActiveCell.Offset(0, 1).Value
Worksheets("Output").Range("C" & place) = ActiveCell.Offset(0, 2).Value
Worksheets("Output").Range("D" & place) = ActiveCell.Offset(0, 3).Value
Worksheets("Output").Range("E" & place) = ActiveCell.Offset(0, 4).Value
Worksheets("Output").Range("F" & place) = ActiveCell.Offset(0, 5).Value
Worksheets("Output").Range("G" & place) = ActiveCell.Offset(0, 6).Value
place = place + 1
Exit For
End If
Next Loopcount
UserForm1.FrameProgress.Caption = Round(((InputCount / ActiveSheet.UsedRange.Rows.count) * 100), 2) & "%"
UserForm1.Label1.Width = (InputCount / ActiveSheet.UsedRange.Rows.count) * 200
DoEvents
Next InputCount
Unload UserForm1
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Instructions").Select
MsgBox "Successfully completed with " & place - 1 & " matches and no errors."
Else
MsgBox "Please make sure there are values for columns: MFPN:, Quantity:, Region:, Date:,Partner Name, and Invoice #"
End If
Else
MsgBox "Please put data into Input page first."
End If
End Sub[/VBA]