PDA

View Full Version : Text Comparison between a column and a table is not working.



kuroba
09-22-2016, 07:51 AM
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
09-24-2016, 09:19 PM
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
09-24-2016, 09:51 PM
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
09-24-2016, 10:49 PM
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
09-25-2016, 06:15 AM
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
09-26-2016, 09:40 AM
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