|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
提问题要说清除
包含本行,以第8行为例
1.第8行往上最近的7行(包含本行):第2行到第8行
2.B-D列出现过的数字:B2:D8
3.在A2:A8没有出现过的数字
想要的结果也要核查下(E9就是错误的B3:D9没有1,自己看附件)
- Function TQX(RNG As Range) '包含本行
- DR = RNG.Row
- If DR < 8 Then
- GoTo 100
- Else
- RMIN = DR - 6
- End If
- arrA = Range(Cells(RMIN, 2), Cells(DR, 4))
- arrB = Range(Cells(RMIN, 1), Cells(DR, 1))
- For i = 1 To UBound(arrA)
- For o = 1 To 3
- If arrA(i, o) = 10 Then
- arrAa = "X"
- Else
- arrAa = arrA(i, o)
- End If
- If Len(TQA) = Len(Replace(TQA, arrAa, "")) Then TQA = TQA & arrAa
- Next o
- Next i
- For j = 10 To 1 Step -1
- If j = 10 Then
- RJ = "X"
- Else
- RJ = j
- End If
- If Len(TQA) <> Len(Replace(TQA, RJ, "")) Then TQA = RJ & Replace(TQA, RJ, "")
- Next j
- For k = 1 To UBound(arrB)
- If arrB(k, 1) = 10 Then
- arrBa = "X"
- Else
- arrBa = arrB(k, 1)
- End If
- If Len(TQA) <> Len(Replace(TQA, arrBa, "")) Then TQA = Replace(TQA, arrBa, "")
- Next k
- 100:
- TQX = Replace(TQA, "X", "10")
- End Function
复制代码 |
评分
-
2
查看全部评分
-
|