My main goal is to compare column B to two workbooks and if anything in the columns doesn't match to put a 0 but leave the matches alone. I attached two examples.
thanks to FloLie for this code however whatever I do there is a subscript out of range in this part of the code
I can't figure it out and I haven't heard anything from him as well.
[FONT=source_sans_proregular]https://mega.nz/#!O8kCFZJC!Y8Notb129OyMEHZtFrL6COrR0b8WSe1VL0B9IAKWb_8
[/FONT][FONT=source_sans_proregular]https://mega.nz/#!jpUSwQKa!RuZUAzuu3grq_ghb9JZ1eSIJRGZJKmJajF1-2cpFlZs[/FONT]
thanks to FloLie for this code however whatever I do there is a subscript out of range in this part of the code
Code:
If compare1(i) = compare2(j) Then
I can't figure it out and I haven't heard anything from him as well.
[FONT=source_sans_proregular]https://mega.nz/#!O8kCFZJC!Y8Notb129OyMEHZtFrL6COrR0b8WSe1VL0B9IAKWb_8
[/FONT][FONT=source_sans_proregular]https://mega.nz/#!jpUSwQKa!RuZUAzuu3grq_ghb9JZ1eSIJRGZJKmJajF1-2cpFlZs[/FONT]
Code:
Sub test() Dim wbSource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim compare1 As Variant, compare2 As Variant, result As Variant
Set wbSource = Workbooks("datafeed1.xlsx")
Set wbTarget = Workbooks("result1.xlsx")
Set wsSource = wbSource.Sheets("datafeed")
Set wsTarget = wbTarget.Sheets("Sheet 1")
compare1 = wsSource.Range("B2", wsSource.Range("B" & Rows.Count).End(xlUp))
compare2 = wsTarget.Range("B2", wsTarget.Range("B" & Rows.Count).End(xlUp))
ReDim result(LBound(compare1), UBound(compare1))
For i = LBound(compare1) To UBound(compare1)
found = False
For j = LBound(compare2) To UBound(compare2)
If compare1(i) = compare2(j) Then
result(i) = 1
found = True
Exit For
End If
Next j
If found = False Then
result(i) = 0
End If
Next i
wsSource.Range(Cells(2, 4), Cells(UBound(result) + 1, 4)) = Results
End Sub