Text Comparison between a column and a table is not working.

[kuroba]

Hello guys,

I'm brand new to the forums, so thanks in advance for your help.
I was wondering if you could help me with this puzzling issue I have with me.

I have 2 tables (used as dynamic variables) that I have input information on each of the tables are only 1 column big and it has values that are use for comparison purposes.
Table A (BAI)

164 165

Table B (Exception)

BOFA

USHW

US H

TRAN

BOCA

305

HEAL

JAME

WARS

592

So the first question that I need to ask is whether any of the cells in the column 3 (looping per row of my buffer sheet) equal anything in Table A(BAI), if true then
Second question, if anything on column 6 (of my buffer sheet) different from Table B (Exception) copy the information to another sheet.
here is the code I have so far, but it doesn't want to work as I needed either coping everything base on table A only and not truly doing a comparison with table B.

Option Explicit

Sub TRANSFER()
Application.Calculation = xlCalculationManual
'FILE INFORMATION
Dim FILE As String, PATH As String
'WORKBOOK
Dim OFxWBxNM As Workbook, DFxWBxNM As Workbook
'WORKSHEET
Dim OFxWSxNM As Worksheet, DFxWSx01 As Worksheet, DFxWSx02 As Worksheet, DFxWSx03 As Worksheet, DFxWSx04 As Worksheet
'LASTROW
Dim OFxWSxLR As Integer, DFxWSxLR As Integer
'CYCLE
Dim iOF As Integer, iDF As Integer, iBAI As Range, iExc As Range
'OTHERS
Dim vExc As Boolean, vTrim As String, vInStr As Long

Set DFxWBxNM = ActiveWorkbook
Set DFxWSx01 = DFxWBxNM.Sheets(1)
Set DFxWSx02 = DFxWBxNM.Sheets(4)
Set DFxWSx03 = DFxWBxNM.Sheets(5)
Set DFxWSx04 = DFxWBxNM.Sheets(6)
FILE = FileOpen("I:\-----Cash Department - Alpharetta RBO----\Alpharetta RBO - Banking\1 Previous Day Summary and Detal\0 - 2016 - Excel", "CashPro", "*.XLS;*.XLSX; *.XLSM")
Set OFxWBxNM = Workbooks.Open(FILE)
Set OFxWSxNM = OFxWBxNM.Sheets(1)
DFxWSx04.Rows("2:65536").Clear
For iOF = 7 To OFxWSxNM.Cells(Rows.Count, 1).End(xlUp).Row
    DFxWSxLR = DFxWSx04.Cells(Rows.Count, 1).End(xlUp).Row + 1
    DFxWSx04.Cells(DFxWSxLR, 1) = DateValue(OFxWSxNM.Cells(iOF, 1))
    DFxWSx04.Cells(DFxWSxLR, 2) = Right(OFxWSxNM.Cells(iOF, 6), 3)
    DFxWSx04.Cells(DFxWSxLR, 3) = Val(OFxWSxNM.Cells(iOF, 10))
    DFxWSx04.Cells(DFxWSxLR, 4).Value = OFxWSxNM.Cells(iOF, 12)
    DFxWSx04.Cells(DFxWSxLR, 5).Value = OFxWSxNM.Cells(iOF, 20)
    DFxWSx04.Cells(DFxWSxLR, 6).Value = Left(OFxWSxNM.Cells(iOF, 20), 4)
Next iOF
For iDF = 2 To DFxWSx04.Cells(Rows.Count, 1).End(xlUp).Row
    'vExc = Left(DFxWSx04.Cells(iDF, 5), 4)
    DFxWSxLR = DFxWSx01.Cells(Rows.Count, 2).End(xlUp).Row + 1
    For Each iBAI In DFxWSx03.Range("BAI")
    For Each iExc In DFxWSx03.Range("Exception")
    If DFxWSx04.Cells(iDF, 3).Value = iBAI.Value Then
    'If DFxWSx04.Cells(iDF, 6).Value = iExc.Value Then
    'If StrComp(DFxWSx04.Cells(iDF, 6), iExc, vbTextCompare) = 0 Then
    If InStr(iExc, DFxWSx04.Cells(iDF, 6)) > 0 Then
    Else
        DFxWSx01.Cells(DFxWSxLR, 2) = DFxWSx04.Cells(iDF, 1)
        DFxWSx01.Cells(DFxWSxLR, 1) = DFxWSx04.Cells(iDF, 2)
        DFxWSx01.Cells(DFxWSxLR, 3) = DFxWSx04.Cells(iDF, 6)
        DFxWSx01.Cells(DFxWSxLR, 5) = DFxWSx04.Cells(iDF, 4)
    End If
    End If
    Next iExc
    Next iBAI
Next iDF

ActiveWorkbook.Close False
Application.Calculation = xlCalculationAutomatic


End Sub

The 2nd comparison statement after ' are the one I've tried and can't make it work. I had a similar issue with the first statement until I started transfering the information using Val()

[mana]

you can use "countif".

For iDF = 2 To DFxWSx04.Cells(Rows.Count, 1).End(xlUp).Row    DFxWSxLR = DFxWSx01.Cells(Rows.Count, 2).End(xlUp).Row + 1
    For Each iBAI In DFxWSx03.Range("BAI")
        If DFxWSx04.Cells(iDF, 3).Value = iBAI.Value Then
            If WorksheetFunction.CountIf(DFxWSx03.Range("Exception"), DFxWSx04.Cells(iDF, 6)) = 0 Then
                DFxWSx01.Cells(DFxWSxLR, 2) = DFxWSx04.Cells(iDF, 1)
                DFxWSx01.Cells(DFxWSxLR, 1) = DFxWSx04.Cells(iDF, 2)
                DFxWSx01.Cells(DFxWSxLR, 3) = DFxWSx04.Cells(iDF, 6)
                DFxWSx01.Cells(DFxWSxLR, 5) = DFxWSx04.Cells(iDF, 4)
            End If
        End If
    Next iBAI
Next iDF

[mana]

you can use "application.match".

Dim m As Variant

DFxWSxLR = DFxWSx01.Cells(Rows.Count, 2).End(xlUp).Row + 1
For iDF = 2 To DFxWSx04.Cells(Rows.Count, 1).End(xlUp).Row
    For Each iBAI In DFxWSx03.Range("BAI")
        m = Application.Match(iBAI, DFxWSx04.Columns(3), False)
        If IsNumeric(m) Then
            If WorksheetFunction.CountIf(DFxWSx03.Range("Exception"), DFxWSx04.Cells(iDF, 6)) = 0 Then
                DFxWSx01.Cells(DFxWSxLR, 2) = DFxWSx04.Cells(m, 1)
                DFxWSx01.Cells(DFxWSxLR, 1) = DFxWSx04.Cells(m, 2)
                DFxWSx01.Cells(DFxWSxLR, 3) = DFxWSx04.Cells(m, 6)
                DFxWSx01.Cells(DFxWSxLR, 5) = DFxWSx04.Cells(m, 4)
            End If
        End If
    Next iBAI
Next iDF

[mana]

sorry, please try this.
Loop of iDF is not need?

Dim m As Variant

For Each iBAI In DFxWSx03.Range("BAI")
    m = Application.Match(iBAI, DFxWSx04.Columns(3), False)
    If IsNumeric(m) Then
        If WorksheetFunction.CountIf(DFxWSx03.Range("Exception"), DFxWSx04.Cells(m, 6)) = 0 Then
            DFxWSxLR = DFxWSx01.Cells(Rows.Count, 2).End(xlUp).Row + 1
            DFxWSx01.Cells(DFxWSxLR, 2) = DFxWSx04.Cells(m, 1)
            DFxWSx01.Cells(DFxWSxLR, 1) = DFxWSx04.Cells(m, 2)
            DFxWSx01.Cells(DFxWSxLR, 3) = DFxWSx04.Cells(m, 6)
            DFxWSx01.Cells(DFxWSxLR, 5) = DFxWSx04.Cells(m, 4)
        End If
    End If
Next iBAI

[Paul_Hossler]

I have 2 tables (used as dynamic variables) that I have input information on each of the tables are only 1 column big and it has values that are use for comparison purposes.
Table A (BAI)

Attaching a small sample workbook with unsensitive data woule make it easier to see

Bottom right corner there is [Go Advanced] and then the paper clip icon

[kuroba]

This worked perfectly for what I needed. In fact, Thanks to it I was able to make the code a lot smaller.
Really appreciate your help and the response of everyone.

Also I will make sure to post a non sensitive sheet if I need a little help for one of my projects Paul. Thanks for the input.

you can use "countif".

For iDF = 2 To DFxWSx04.Cells(Rows.Count, 1).End(xlUp).Row    DFxWSxLR = DFxWSx01.Cells(Rows.Count, 2).End(xlUp).Row + 1
    For Each iBAI In DFxWSx03.Range("BAI")
        If DFxWSx04.Cells(iDF, 3).Value = iBAI.Value Then
            If WorksheetFunction.CountIf(DFxWSx03.Range("Exception"), DFxWSx04.Cells(iDF, 6)) = 0 Then
                DFxWSx01.Cells(DFxWSxLR, 2) = DFxWSx04.Cells(iDF, 1)
                DFxWSx01.Cells(DFxWSxLR, 1) = DFxWSx04.Cells(iDF, 2)
                DFxWSx01.Cells(DFxWSxLR, 3) = DFxWSx04.Cells(iDF, 6)
                DFxWSx01.Cells(DFxWSxLR, 5) = DFxWSx04.Cells(iDF, 4)
            End If
        End If
    Next iBAI
Next iDF