1.  统计及取不重复值  by:oobird

By:oobird

Sub Macro1()
Dim w
 w = "Sheet1!" & Application.ConvertFormula(Sheet1.UsedRange.Address, xlA1, xlR1C1, 1)  ‘转换公式成地址转换
‘”Sheet1!R1C1:R14C4”
With Sheet2
.UsedRange.Clear
.[a1].Consolidate w, xlSum, 1, 1   ‘Sources:=w,Function:=xlSum,TopRow:=True,LeftColumn:=True
End With
End Sub
 
Sub yy()
'by:oobird
Dim n%
  With Sheet1
    n = .[a1].End(xlDown).Row   ‘A1是字段名
    .Range("A1:A" & n).AdvancedFilter 2, , Sheet2.[a1], 1
  End With
End Sub
 
Match方法：
查找数组第2列里有“A”字符串：
arr = [a1:b10]
arr2 = Application.Index(arr, , 2)
If IsError(Application.Match("A", arr2, 0)) Then
   MsgBox "不包含A"
Else
   MsgBox "包含A"
End If
     
Filter方法：
If UBound(Filter(Application.Transpose(arr2), "A")) >= 0 Then MsgBox "包函A"