|
本帖最后由 endupbkb 于 2017-3-27 23:46 编辑
代码如下:
Sub txsj()
Dim i&, Myr&, rq, rq1, sx1, sx2, Arr
Application.ScreenUpdating = False
rq1 = Sheet1.[b2] - 1
rq = Sheet1.[b2] - 7
sx1 = Sheet1.[b5]
sx2 = Sheet1.[b6]
Myr = Sheet2.Cells(Rows.Count, 7).End(xlUp).Row
Arr = Sheet2.Range("a2:as" & Myr)
For i = 1 To UBound(Arr)
If Arr(i, 12) <= rq1 And Arr(i, 12) >= rq And Arr(i, 31) = sx1 And Arr(i, 32) = sx2 Then
n = n + 1
Sheet3.Cells(n + 1, 1).Resize(1, 45) = Application.Index(Arr, i, 0)
End If
Next
Sheet3.[a1].Resize(1, 45) = Sheet2.Rows(1)
Application.ScreenUpdating = True
End Sub
运行后发现数组Arr(120)符合if条件,但是运行Application.Index(Arr, i, 0)时数组中有空值导致报错:数据类型不匹配
请教各位老师,我该如何修改,万分感谢!
- Sub txsj()
- Dim i&, Myr&, rq, rq1, sx1, sx2, Arr
- Application.ScreenUpdating = False
- rq1 = Sheet1.[b2] - 1
- rq = Sheet1.[b2] - 7
- sx1 = Sheet1.[b5]
- sx2 = Sheet1.[b6]
- Myr = Sheet2.Cells(Rows.Count, 7).End(xlUp).Row
- Arr = Sheet2.Range("a2:as" & Myr)
- For i = 1 To UBound(Arr)
- If Arr(i, 12) <= rq1 And Arr(i, 12) >= rq And Arr(i, 31) = sx1 And Arr(i, 32) = sx2 Then
- n = n + 1
- Sheet2.Rows(i + 1).Copy Sheet3.Cells(n + 1, 1)
- End If
- Next
- Sheet2.Rows(1).Copy Sheet3.[a1]
- Application.ScreenUpdating = True
- End Sub
复制代码简单改了一下
|
|