|
从sheet (1)筛选出 #N/A 值的列后, 汇总到数组,copy 到另外一个sheet 中 (以便进行下一步处理).
其间出现一个 type mismatch 的debug(如下代码红色段). 试验了很长段时间,未能解决. 恳请高手帮忙指点. 谢谢!- Sub 筛选空值并一起复制到新的表格()
- Dim arr, arr1(), y As Long, i As Long, R As Long
- With Sheets("ZOSO")
- arr = .Range("C2:G" & .Range("C1048576").End(xlUp).Row).Value <FONT color=blue>'</FONT><FONT color=navy>赋值sheet("ZOZO")中C2到G列最后一列有值的区域为二维数组(1to"Range("C1048576").End(xlUp).Row", 1to5)
- </FONT>End With
- R = Range("C1048576").End(xlUp).Row <FONT color=navy>'定义r为 sheets("ZOSO")中的有值列的范围
- </FONT>For y = 1 To UBound(arr)
- <FONT style="BACKGROUND-COLOR: yellow">If arr(y, 5) = "#N/A" Then
- </FONT>i = i + 1
- ReDim Preserve arr1(1 To 5, 1 To i) <FONT color=navy>'筛选出数组arr中#N/A值的元素,并归类为新的二维数组 arr1
- </FONT>arr1(1, i) = arr(y, 1)
- arr1(2, i) = arr(y, 2)
- arr1(3, i) = arr(y, 3)
- arr1(4, i) = arr(y, 4)
- arr1(5, i) = arr(y, 5)
- End If
- Next y
- Sheets("IPES data").Activate <FONT color=navy>'把新的数组arr1取得的值复制到sheets("IPES")
- </FONT>Range("A" & R).Resize(UBound(arr1, 2), UBound(arr1)) = Application.Transpose(arr1)
- Range("A" & R).Resize(UBound(arr1, 2), UBound(arr1)).Borders.LineStyle = 1
- Range("A" & R + UBound(arr1, 2)).Select
- End Sub
复制代码
- Sub 筛选空值并一起复制到新的表格()
- Dim arr, arr1(), y As Long, i As Long, R As Long
- With Sheets("ZOSO")
- arr = .Range("C2:G" & .Range("C1048576").End(xlUp).Row).Value '赋值sheet("ZOZO")中C2到G列最后一列有值的区域为二维数组(1to"Range("C1048576").End(xlUp).Row", 1to5)
- End With
- R = Range("C1048576").End(xlUp).Row '定义r为 sheets("ZOSO")中的有值列的范围
- For y = 1 To UBound(arr)
- If VarType(arr(y, 5)) = vbError Then
- i = i + 1
- ReDim Preserve arr1(1 To 5, 1 To i) '筛选出数组arr中#N/A值的元素,并归类为新的二维数组 arr1
- arr1(1, i) = arr(y, 1)
- arr1(2, i) = arr(y, 2)
- arr1(3, i) = arr(y, 3)
- arr1(4, i) = arr(y, 4)
- arr1(5, i) = arr(y, 5)
- End If
- Next y
- Sheets("IPES data").Activate '把新的数组arr1取得的值复制到sheets("IPES")
- Range("A" & R).Resize(UBound(arr1, 2), UBound(arr1)) = Application.Transpose(arr1)
- Range("A" & R).Resize(UBound(arr1, 2), UBound(arr1)).Borders.LineStyle = 1
- Range("A" & R + UBound(arr1, 2)).Select
- End Sub
复制代码
|
|