Comparing Rows / Counting Rows

[johnkrenkel]

Hoping for a more streamlined piece of code. This basically compares multiple columns in a row to see if that information is in a string (row) on another sheet.

It works great but its bulky and slow as it is repeating itself over and over again. works great for small files but when i run it through a lot of data it takes some time.

I was thinking of using an array but not sure how to do that using INSTR or if there is a better way.

Function getcount()

Dim i As Integer
Dim j As Integer
Dim rng As Range, cel As Range   ' Account List
Dim rng2 As Range, cel2 As Range ' Master Data Sheet
Dim rng3 As Range
Dim lastrow As Long
Dim lastrow2 As Long
Dim LR3 As Long
Dim rngItemInRvw As Range
Dim rngCountSum As Range




i = 0
j = 0
Row_num = 1
row_num2 = 1
Application.ScreenUpdating = False


' List Range (account name)
lastrow = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
Set rng = Sheets("List").Range("A2:A" & lastrow - 1) 'Adjust as necessary


'Data Range
lastrow2 = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Master").Range("A1:A" & lastrow2 - 1) 'Adjust as necessary




Do
DoEvents
    Row_num = Row_num + 1
    item_in_review = ThisWorkbook.Worksheets("list").Range("a" & Row_num)
    Item_in_review2 = ThisWorkbook.Worksheets("list").Range("c" & Row_num)
    IIR_Code1 = ThisWorkbook.Worksheets("list").Range("d" & Row_num)
    IIR_Code2 = ThisWorkbook.Worksheets("list").Range("e" & Row_num)
    IIR_Code3 = ThisWorkbook.Worksheets("list").Range("f" & Row_num)
    IIR_Code4 = ThisWorkbook.Worksheets("list").Range("g" & Row_num)
    IIR_Code5 = ThisWorkbook.Worksheets("list").Range("h" & Row_num)
    
    
        If item_in_review <> "" Then
    
            Do
            DoEvents
                    
            search_string = ThisWorkbook.Worksheets("Master").Range("a" & row_num2)
            
                If ThisWorkbook.Worksheets("List").Range("d" & Row_num) = "" Then
                    If InStr(1, search_string, item_in_review) > 0 And InStr(1, search_string, Item_in_review2) > 0 Then
                    i = i + 1
                    End If
                Else
                If ThisWorkbook.Worksheets("List").Range("d" & Row_num) <> "" Then
                    If InStr(1, search_string, item_in_review) > 0 And InStr(1, search_string, Item_in_review2) And InStr(1, search_string, IIR_Code1) > 0 Or InStr(1, search_string, IIR_Code2) > 0 Or InStr(1, search_string, IIR_Code3) > 0 Or InStr(1, search_string, IIR_Code4) > 0 Or InStr(1, search_string, IIR_Code5) > 0 Then
                    i = i + 1
                    End If
                End If
             End If
           
            row_num2 = row_num2 + 1
       
        Loop Until row_num2 = lastrow2


rngiteminreview = Sheets("list").Range("a" & Row_num).Offset(0, 8).Select
Selection.Value = i
End If


   i = 0
   row_num2 = 1
   
   
  Loop Until item_in_review = ""
  
 Row_num = 1
row_num2 = 1


 
' List Range (account name) and Sum of Count
lr = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
Set rng3 = Sheets("List").Range("A2:A" & lr) 'Adjust as necessary


   With Sheets("List")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    Set rngCountSum = Sheets("list").Range("I" & lr + 2)
    rngCountSum.Value = WorksheetFunction.Sum(.Range("I1:I" & lr))
  End With
  Application.ScreenUpdating = True
 


End Function

 

[BobbyMaynard]

wish i could help you bud, but your obviously alot more advanced at excel than I