Snasui Excel Forum

Code: Select all

Sub searchNum()

    Dim x As Long, y As Long, z As Long
    Dim lRow As Long, r As Range
    
    Sheets("All").Activate
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 2 To lRow       ' x for loop row
        For y = 2 To 3      ' y for loop column
            z = 0
            For Each r In Range(Names(Cells(1, y).Value).RefersTo)
                If InStr(r.Value, Range("a" & x).Value) > 0 Then
                    z = z + 1
                End If
            Next r
            Cells(x, y).Value = z
        Next y
    Next x
    
End Sub

Code: Select all

lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lRow       ' x for loop row
        For y = 2 To 3      ' y for loop column
            Cells(x, y).Value = WorksheetFunction. _
            SumProduct(1 * IsNumber(Search(Range("a" & x), INDIRECT(Cells(1, y)))))
        Next y
    Next x