Text Comparison between a column and a table is not working.
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)
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.
Code:
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()
