for..next

WorksheetFunction.HLookup(opex_total.Cells(i, j).Value, mass(),  1 , False) = "#Н/A"

  
Sub summary()
     Dim i As Long
     Dim j As Long
     Dim k As Long
     Dim n As Long
     Dim rowmax As Long
     Dim opex_total As Object, basebookname As String
     basebookname = ThisWorkbook.Name
     Set opex_total = Workbooks(basebookname).Sheets("OPEX_TOTAL")
   j =  6 
   rowmax = Cells(opex_total.Rows.Count, j).End(xlUp).Row
   n = rowmax -  6 
   Dim mass(n) As Variant
  k =  1 
  For i =  7  To rowmax
    If WorksheetFunction.HLookup(opex_total.Cells(i, j).Value, mass(),  1 , False) = "#Н/A" Then
      mass(k) = opex_total.Cells(i, j).Value
      k = k +  1 
    End If
  Next
End Sub

Application.Hlookup()
WorksheetFunction.Hlookup()