|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 WYS67 于 2019-12-20 13:32 编辑
下面的代码,第一参数数据源既可以指定数据区域用区域数组公式输入,也可以指定单元格用普通公式计算单值。用普通公式输入时,如果数据区域下方存在看不见的字符,就会显示#VALUE!错误;但用区域数组公式输入则可以自动屏蔽错误为空白。
怎样处理才能在用普通公式输入时,也可以忽略看不见的字符为空白?
貌似 把 If arr(i, 1) <> "" Then改为 If IsNumeric(arr(i, 1)) Then 之类就可以解决问题,
只是苦于不知加在哪里合适?请老师们帮忙给予提示。
代码如下:
Function LRQUYU(a As Range, Optional b = "", Optional f = "")
Dim c&, d&, e&, i&, j&, k&, x$, y$, ar, br, cr$(), dr, er, at
If a.Count = 1 Then
ReDim ar(1 To 1, 1 To 1): ar(1, 1) = a
If a = "" Then LRQUYU = "": Exit Function
Else: ar = a
End If
dr = b: ReDim cr(1 To UBound(ar), 0)
For i = UBound(ar) To 1 Step -1
If ar(i, 1) <> "" And IsNumeric(ar(i, 1)) Then d = i: Exit For
Next
If UBound(ar, 2) = 1 Then
c = Len(ar(d, 1)): ReDim br(1 To d, 1 To c)
For i = 1 To d
For j = 1 To c
br(i, j) = Mid(ar(i, 1), j, 1)
Next
Next
Else: c = UBound(ar, 2): br = a
End If
ReDim er(1 To c)
For i = 1 To d
If ar(i, 1) <> "" And IsNumeric(ar(i, 1)) Then
y = "": x = "": m = 0
For j = 1 To c
If Not IsArray(dr) Then
For k = 0 To c - 1
If br(i, j) Mod c = k Then Exit For
Next
y = y & k + 1
Else
For k = 1 To c
If k = c Then e = b(k + 1) + 1 Else e = b(k + 1)
If CLng(br(i, j)) >= b(k) And CLng(br(i, j)) < e Then Exit For
Next
y = y & k
End If
Next
For j = 1 To c
If f = "" Or f = 0 Then
If InStr(y, j) = 0 Then x = x & j - 1
Else
If Len(y) - Len(Replace(y, j, "")) > 1 Then
m = m + 1: x = x & j - 1: er(m) = Len(y) - Len(Replace(y, j, ""))
End If
End If
Next
If Application.Max(er) > 2 Then
t = ""
Set at = CreateObject("System.Collections.ArrayList")
For j = 1 To m: at.Add (er(j)) & Mid(x, j, 1): at.Sort: Next
For j = m To 1 Step -1: t = t & Mid(at.Item(j - 1), 2, 1): Next
x = t
End If
If x = "" Then cr(i, 0) = c Else cr(i, 0) = x
Else: cr(i, 0) = ""
End If
Next
LRQUYU = cr
End Function
|
|