大家好:
如果想要从一列有空格的名单中(假设b列)筛选出重复的名单,并将其放入另外一列中(假设m列),我编写了一段程序,可老出现运行错误“13”,类型不匹配,恳请各位帮忙解决下!不甚感激!
Sub jszq()
Dim myrow1 As Integer
Dim x As Integer, y As Integer, n As Integer
Dim AA, BB, CC
n = 1
Cells(1, 13) = 0
Range("b1").Select
myrow1 = [b65536].End(xlUp).Row
For y = 2 To myrow1
BB = 0
CC = 0
AA = 0
AA = Cells(y, 2)
BB = Application.WorksheetFunction.Search(AA, Range("b2:b" & y - 1)) '看现在的单元格是否在前几行出现过
CC = Application.WorksheetFunction.Search(AA, Range("m1:m" & n)) '看现在的单元格是否已被列入M列
If AA = "" Or CC > 0 Then '若出现无名单的单元格或者已被列入名单的名单,跳过
GoTo 100
ElseIf BB > 0 Then
Cells(n, 13) = Cells(y, 2) '否则若在前几行出现过写入M列
n = n + 1
Else
End If
100: Next y
End Sub
如果想要从一列有空格的名单中(假设b列)筛选出重复的名单,并将其放入另外一列中(假设m列),我编写了一段程序,可老出现运行错误“13”,类型不匹配,恳请各位帮忙解决下!不甚感激!
Sub jszq()
Dim myrow1 As Integer
Dim x As Integer, y As Integer, n As Integer
Dim AA, BB, CC
n = 1
Cells(1, 13) = 0
Range("b1").Select
myrow1 = [b65536].End(xlUp).Row
For y = 2 To myrow1
BB = 0
CC = 0
AA = 0
AA = Cells(y, 2)
BB = Application.WorksheetFunction.Search(AA, Range("b2:b" & y - 1)) '看现在的单元格是否在前几行出现过
CC = Application.WorksheetFunction.Search(AA, Range("m1:m" & n)) '看现在的单元格是否已被列入M列
If AA = "" Or CC > 0 Then '若出现无名单的单元格或者已被列入名单的名单,跳过
GoTo 100
ElseIf BB > 0 Then
Cells(n, 13) = Cells(y, 2) '否则若在前几行出现过写入M列
n = n + 1
Else
End If
100: Next y
End Sub
在VB里,可以直接这样使用吗?
问题很有可能出现在以下两行上:
BB=Application.WorksheetFunction.Search(AA,Range("b2:b" & y - 1)) '看现在的单元格是否在前几行出现过
CC=Application.WorksheetFunction.Search(AA,Range("m1:m" & n)) '看现在的单元格是否已被列入M列
Sub jszq()
Dim myrow1 As Integer, ddd
myrow1 = [b65536].End(xlUp).Row
With Range("M1:M" & myrow1)
.FormulaR1C1 = "=IF(COUNTIF(R1C2:RC[-11],RC[-11])=2,RC[-11],"""")"
ddd = .Value: .Value = ddd
.Sort Key1:=Range("M1")
End With
End Sub
Sub jszq()
Dim ddd()
With Range("M1:M" & [B65536].End(xlUp).Row)
.FormulaR1C1 = "=IF(COUNTIF(R1C2:RC[-11],RC[-11])=2,RC[-11],"""")"
ddd = .Value: .Value = ddd
.Sort Key1:=Range("M1")
End With
End Sub
M1单元格=IF(COUNTIF($B$1:B1,B1)=2,B1,"")
M2单元格=IF(COUNTIF($B$1:B2,B2)=2,B2,"")
M3单元格=IF(COUNTIF($B$1:B3,B3)=2,B3,"")
.
.
.
具体思路:
假定当前为第8行
1.在M8中输入公式,统计B8的值在 B1:B8 中的个数,如果是2 ,说明有两个重复,取出B8,放在M8;如果为0 或 1,说明没有重复,M8="";如果>2,说明有两个以上的重复值,也就说明前面已经去过,可不用再取,M8="".这样在M列中就得到只有重复值得B列的值,对M列排序,重复的值就到了上边,完成了你的要求。如:
B列 M列公式 M列得到的结果 M列排序后结果
B1:A =IF(COUNTIF($B$1:B1,B1)=2,B1,"") A
B2:B =IF(COUNTIF($B$1:B2,B2)=2,B2,"") B
B3: =IF(COUNTIF($B$1:B3,B3)=2,B3,"") C
B4:d =IF(COUNTIF($B$1:B4,B4)=2,B4,"")
B5:C =IF(COUNTIF($B$1:B5,B5)=2,B5,"")
B6:B =IF(COUNTIF($B$1:B6,B6)=2,B6,"") B
B7: =IF(COUNTIF($B$1:B7,B7)=2,B7,"")
B8:E =IF(COUNTIF($B$1:B8,B8)=2,B8,"")
B9:A =IF(COUNTIF($B$1:B9,B9)=2,B9,"") A
B10:A =IF(COUNTIF($B$1:B10,B10)=2,B10,"")
B11:C =IF(COUNTIF($B$1:B11,B11)=2,B11,"") C我测试过,没问题
Sub jszq()
Dim ddd()
With Range("M1:M" & [B65536].End(xlUp).Row)
'输入公式
.FormulaR1C1 = "=IF(COUNTIF(R1C2:RC[-11],RC[-11])=2,RC[-11],"""")"
'将公式的计算结果转为值,相当于"复制-选择性粘贴-数值"
ddd = .Value: .Value = ddd
'对M列排序
.Sort Key1:=Range("M1")
End With
End Sub
Sub jszq()
Dim y As Integer, n As Integer
Dim BB As Long
n = 1
For y = 2 To [b65536].End(xlUp).Row
BB = Application.WorksheetFunction.CountIf(Range("b1:b" & y), Cells(y, 2)) '看现在的单元格是否在前几行出现过
If BB = 2 Then
Cells(n, 13) = Cells(y, 2) '否则若在前几行出现过一次写入M列,多于一次都不写
n = n + 1
End If
Next y
End Sub
For y = 2 To [b65536].End(xlUp).Row
if Application.WorksheetFunction.CountIf(Range("b1:b" & y), Cells(y, 2)) >1 then '看现在的单元格是否在前几行出现过
Cells([M65536].end(XLUP).row+1,13) = Cells(y, 2)
End If
Next y
end sub
别乱改,if 条件不能>1,如有3个, 3>1,再写入M列,M列又重复了
[M65536].end(XLUP).row+1 如果开始时M1没内容,那么地一个重复值就会写入第二行,你好好分析分析再改。
别乱改,if 条件不能> 1,如有3个,3> 1,再写入M列,M列又重复了
[M65536].end(XLUP).row+1 如果开始时M1没内容,那么第一个重复值就会写入第二行,你好好分析分析再改。
Sub 第二步()
Dim my1 As Integer, my2 As Integer
Dim m%, n%
Dim AA
Range("b1").Select
my1 = [b65536].End(xlUp).Row
Range("k1").Select
my2 = Selection.CurrentRegion.Rows.Count
For n = 1 To my1
If Cells(n, 2) = "" Or Cells(n, 2) = "姓名" Then
GoTo 100
AA = Application.WorksheetFunction.CountIf(Range("k1:k" & my2), Cells(n, 2))‘在已列出的重复名单中看有没有现在要查找的名字
ElseIf AA = 1 Then
Cells(n, 2).Interior.ColorIndex = 3 ’若有则填充红色
End If
AA = 0
100: Next n
End Sub另外一种方法和筛选重复名单方法类似,但是第一次出现的重复名单却不能填充
Sub 第一步2()
Dim y As Integer, n As Integer
Dim BB As Long
n = 1
For y = 2 To [b65536].End(xlUp).Row
BB = Application.WorksheetFunction.CountIf(Range("b1:b" & y - 1), Cells(y, 2)) '看现在的单元格是否在前几行出现过
If BB >= 1 Then
Cells(y, 2).Interior.ColorIndex = 3
n = n + 1
End If
Next y
End Sub
For n = 1 To my1
If Cells(n, 2) = "" Or Cells(n, 2) = "姓名" Then
GoTo 100
AA = Application.WorksheetFunction.CountIf(Range("k1:k" & my2), Cells(n, 2))‘在已列出的重复名单中看有没有现在要查找的名字
ElseIf AA = 1 Then
Cells(n, 2).Interior.ColorIndex = 3 ’若有则填充红色
End If
AA = 0
100: Next n
End Sub
红色的goto 100若放在这里,那么Countif就永远不会执行,AA什么时候能=1呢?做如下改动:
Sub 第一步2()
Dim y As Integer, n As Integer
Dim BB As Long
For y = 2 To [b65536].End(xlUp).Row
BB = Application.WorksheetFunction.CountIf(Range("b1:b" & y - 1), Cells(y, 2))
If BB >= 1 Then
Cells(y, 2).Interior.ColorIndex = 3
n = Application.WorksheetFunction.Match(Cells(y, 2), Range("b1:b" & y - 1), 0)
Cells(n, 2).Interior.ColorIndex = 3 End If
Next y
End Sub
如果重复次数不是很多,可用此方法,次数太多则程序有些累赘,因为每次都给第一次的重复值设置红色。
祝你早日完成。