I have Excel Sheet A which has ~700 rows of data and Sheet B which has ~100,000 rows of data and I need to compare each line of Sheet A to each line of Sheet B. I use simple counters to add rows, starting from Sheet A's first row, compare it to Sheet B's each row(or until the match has been found), then add 1 to row counter and compare second row of Sheet A to each row of Sheet B and so on. This takes really long time(roughly one hour), so I was wondering if there is a smarter/faster way to do this? Columns are not same in these worksheets, I just compare these columns like If A = a and B = b and C = c Then write value from certain column to Sheet C.
Somebody from MrExcel forum suggested using CountIf WorksheetFunctionf and I came up with this code:
Application.StatusBar = "Writing sales figures..."
Dim yh As Integer
Dim zh As String
Dim ai As Integer
ai = Worksheets("Abbrev").Cells(1, "I") + 1 ' max rows in this sheet, ~700...
' MsgBox (ai)
' check max rows from "Taul4"...
Dim maxTaul4Rows As Long
maxTaul4Rows = Worksheets("Taul4").Cells(Rows.count, 3).End(xlUp).Row ' will be used in final code with Taul4.Range...
' MsgBox (maxTaul4Rows)
Dim salesFigure As Double ' need this one to be returned...
' Dim taul4RowCounter As Long
' taul4RowCounter = 2 ' row 1 has headers from DB...
Dim bi As Integer
bi = 1
Dim ci As Integer
ci = 3
Application.EnableEvents = False
Do Until bi > ai
zh = Worksheets("Abbrev").Cells(bi, "F")
yh = Worksheets("Abbrev").Cells(bi, "E")
Do Until ci = 15
'On Error Resume Next
On Error GoTo errhandler
If WorksheetFunction.CountIf(Worksheets("Taul4").Range("D1:D100000"), Worksheets("Abbrev").Cells(bi, "C").Value) Then
   If WorksheetFunction.CountIf(Worksheets("Taul4").Range("B1:B100000"), ci) Then
      If Worksheets("Abbrev").Cells(bi, "J") = "0" Then
         ' salesFigure = Colunm "A" from Worksheets("Taul4")... this value needs to be returned every time those matches are found...
         ' Worksheets(zh).Cells(yh, ci) = salesFigure
      End If
   End If
End If
ci = ci + 1
bi = bi + 1
Application.StatusBar = bi
ci = 3
Application.EnableEvents = True
MsgBox ("Done...")
Application.StatusBar = ""
Exit Sub
Application.StatusBar = ""
MsgBox (Err.Description)
but I have no idea how to return the value of any particular column of that row, and not sure if it can be done at the first place or if there's another way to do it.