|
本帖最后由 xuesheng1 于 2015-4-16 18:23 编辑
各位老师您们好,我是新手刚学VBA,看视频后感想颇多,想到我有大量数据要处理特求助老师们给与帮助,万分感谢!
由于数据量很大,我仅是截取一部分,请帮助用内存数组处理,依次提取附带的“ku”工作薄的每个工作表数据至a:l 列(或者直接提至内存更好)
if(A2:H2>=N2:U2,if(I2=5,5,4),0) (对应单元全部大于,这个公式不对仅为描述方便)
if(A3:H3>=N2:U2,if(I3=5,5,4),0)
if(A4:H4>=N2:U2,if(I4=5,5,4),0)
循环至结尾,统计5和4的个数(这步统计后也可放入单元格),之后
if(A2:H2>=N3:U3,if(I2=5,5,4),0)
if(A3:H3>=N3:U3,if(I3=5,5,4),0)
结果放入 w x 列 万分感谢!!!
- Sub lqxs()
- Dim myPath$, myName$, Arr1, Sh As Worksheet
- Dim i&, Brr, Myr&, m5&, m4&, y&, j&, Arr, Crr
- Application.ScreenUpdating = False
- Sheet1.Activate
- [w:x].ClearContents
- myPath = ThisWorkbook.Path & ""
- myName = "ku.xls"
- With GetObject(myPath & myName)
- For Each Sh In .Sheets
- Arr1 = Sh.Range("A1").CurrentRegion
- Myr = Cells(Rows.Count, 1).End(xlUp).Row + 1
- Cells(Myr, 1).Resize(UBound(Arr1), UBound(Arr1, 2)) = Arr1
- Next
- .Close False
- End With
- Arr = [a1].CurrentRegion
- Brr = [n1].CurrentRegion
- ReDim Crr(1 To UBound(Brr), 1 To 2)
- For i = 2 To UBound(Brr)
- m5 = 0: m4 = 0
- For j = 1 To UBound(Arr)
- For y = 1 To 8
- If Arr(j, y) < Brr(i, y) Then GoTo 100
- Next
- If Arr(j, 9) = 5 Then m5 = m5 + 1 Else m4 = m4 + 1
- 100:
- Next
- Crr(i, 1) = m5: Crr(i, 2) = m4
- Next
- [w1].Resize(UBound(Crr), 2) = Crr
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|