|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 WYS67 于 2019-2-1 22:09 编辑
老师:请看截图:《按指定期序提取相关数据》里的代码。指定C1为数据源I:J列最后行号,当前是4540。指定H1的最大行号为10252,当H1大于C1的最后行号4540时,E4541:J10252就会显示#N/A错误!怎样把#N/A屏蔽为空白?恳请老师帮忙解决!
代码如下:Sub 按指定期序提取相关数据()
Dim ar, br, i&, j&, k&, x&, y&, h1, h2, f2,c2
f2 = Cells(2, "f"): h2 = Cells(2, "h"): c2 = Cells(2 ,"c")
If f2 = "" And h2 <> "" Then MsgBox "f2:h2指定期序有误!": Exit Sub
Windows("00 总表.xlsm").Activate
ar = Range("e5:j" & Cells(2, "h"))
For k = UBound(ar) To 1 Step -1
If ar(k, 1) <> "" Then Exit For
Next
x = UBound(ar, 2): ReDim br(1 To k, 1 To x)
For i = 1 To k
If f2 = "" And h2 = "" Then
If ar(i, 4) = c2 Then
y = y + 1: For j = 1 To x: br(y, j) = ar(i, j): Next
End If
ElseIf f2 <> "" And h2 = "" Then
If ar(i, 4) = f2 Then
y = y + 1: For j = 1 To x: br(y, j) = ar(i, j): Next
End If
ElseIf f2 <> "" And h2 <> "" Then
If ar(i, 4) >= f2 And ar(i, 4) <= h2 Then
y = y + 1: For j = 1 To x: br(y, j) = ar(i, j): Next
End If
End If
Next
Windows("按指定期序提取相关数据.xlsm").Activate
Worksheets("总表").Select
h1 = Cells(1, "h"): [e5].Resize(h1 - 4, UBound(br, 2)) = br
End Sub
河南快三.zip
(1.15 MB, 下载次数: 6)
|
|