Count Occurrences of Word in report string AND write result to E column of list tab

[johnkrenkel]

I am trying to match data from one tab to another (or one string to another). The first part comes from an account list which is the client name. I think want to run through a daily report that is just a bunch of strings in the A columns.

I can walk thru the code and see it is looking at the right rows but the problem is, it is adding to the counter everytime i get an empty line in the Master Sheet. I don't understand why it would add to the count when there isn't match

in this instance, we're looking at A2 = client name in LIST sheet. Going to the A column in the Master Sheet.

Everytime the Client Name and ID appear in the Master sheet, it should add to I. instead i am getting a lot of false hits.

Also, know this is wrong because i am trying to right an integer into a range but i am a bit stuck (which i am sure is just an easy fix)

Sheets("List").Cells(rng, 1).Offset(0, 4).Value = i

It's been a while since i coded.

Function getcount()

Dim i As Integer
Dim rng As Range, cel As Range   ' Account List
Dim rng2 As Range, cel2 As Range ' Master Data Sheet
Dim lastrow As Long
Dim lastrow2 As Long


i = 0


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


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




For Each cel In rng 'Move through Account List Range
   For Each cel2 In rng2 'Move through Master Data Range


        If InStr(1, cel.Value, cel2.Value, vbTextCompare) & InStr(1, cel.Offset(0, 2).Value, cel2.Value, vbTextCompare) <> 0 Then
            i = i + 1
        End If
   
   Next cel2
   
  'writes count of i in Count Column
  Sheets("List").Cells(rng, 1).Offset(0, 4).Value = i
   
  i = 0
 Next cel


End Function

 

[DanteAmor]

With a function you can not write in several cells, in this case you need a macro, for each List data in column A look for it in Master and count.

Try this

Sub Count_Items()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim b As Range, r As Range, c As Range
    Dim wCell As String, i As Long
    
    Set sh1 = Sheets("List")
    Set sh2 = Sheets("Master")
    
    For Each c In sh1.Range("A1", sh1.Range("A" & Rows.Count).End(xlUp))
        i = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(c.Value, LookAt:=xlPart, LookIn:=xlValues)
        If Not b Is Nothing Then
            wCell = b.Address
            Do
                If InStr(1, b.Offset(0, 2).Value, c.Offset(0, 2).Value) > 0 Then i = i + 1
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> wCell
        End If
        c.Offset(0, 4).Value = i
    Next
End Sub