更快找到方法和过滤器位置比较？ [英] Faster Find Method and filter position comparison?

问题描述

问题：我必须搜索一张大单以获得具体的政策号码。当有近75,000行时，查找功能需要相当长的一段时间。有关如何比较这两张75,000行的建议？我认为可能的一个解决方案是排序每个工作表，然后采取需要找到的政策编号，并将其与中间行进行比较。有没有办法比较该政策编号，看看在简单的排序功能是否大于或小于？找到这个比较后，我会重新设置上限和下限，再次找到中间。这会更快吗？还有其他建议吗？

谢谢

当前代码：

  Sub policyComment（）
 
 Dim x As Integer 
 Dim endRow As Variant 
 Dim polSer As String 
 Dim foundVal As String 
 Dim commentVar As Variant 
 
 Windows（SuspenseNoteMacro.xlsm）。激活
表格（Main）。选择
 
 Range（A2）。选择
 endRow = ActiveCell.End（xlDown）
 
x = 2 
 
 Do 
 polSer = Range $ CStr（x））。$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Set $ Set Set Set Set Set Set Set Set Set Set Set Set Set Set Set Set Set Set findRange = Sheets（Sheet1）。Cells.Find（what：= polSer，LookIn：= xlFormulas，lookat：= xlWhole）
 
'foundRange = ActiveCell.Value 
如果foundRange不是然后
 Windows（SuspenseNoteMacro.xlsm）。激活
表格（Main）。选择
 Range（J+ CStr（x））。Value =Not Found
 ElseIf foundRange<> 然后
 Sheets（Sheet1）。Cells.Find（what：= polSer，LookIn：= xlFormulas，lookat：= xlWhole）.Activate 
 commentVar = Range（J+ CStr（ActiveCell 
 Range（J+ CStr（x）））值= 
 Windows（SuspenseNoteMacro.xlsm）激活
表格（Main commentVar 
如果
 
x = x + 1 
范围（A+ CStr（x））。选择
 foundRange =
循环直到（x = endRow）
 
 End Sub 
  

解决方案>

您的代码很慢，原因很简单，主要是因为您单独循环使用每个单元格（实际查找函数不是减慢它的速度）。

下面，我将搜索列放入一个数组中，并循环遍历，这将快得多。我也删除了所有的选择和激活语句，因为它们在VBA中99％的时间是无关紧要的，还可以减慢你的代码。最后，我关闭了 ScreenUpdating ，这也有帮助。

如果我错过了重构中的某些内容，请告诉我。

  Option Explicit 
 
 Sub policyComment（）
 
 Dim x As Long，endRow As Long，polSer As String，foundRange As range，commentVar As String 
 Dim varArr（）As Variant 
 Dim wksMain As Worksheet，wks1 As Worksheet 
 
设置wksMain = Sheets（Main）
设置wks1 = Sheets（Sheet1）
 
 Application.ScreenUpdating = False 
 
带有wksMain 
 
 endRow = .range（A& .Rows.Count）.End（xlUp）.Row 
 varArr = .range（A2：A& endRow）
 
对于x = LBound（varArr）到UBound（varArr）
 
 polSer = varArr（x，1）
 
使用wks1 
 
设置foundRange = .Cells。 Find（polSer，LookIn：= xlFormulas，lookat：= xlWhole）
 
如果foundRange不是，然后
 
 wksMain.range（J& x + 1）.Value =不发现'需要添加1到x，因为数组是零为基础
 
 Else 
 
 commentVar = .range（J& findRange.Row）
 wksMain.range（J& x + 1）.Value = commentVar需要添加1到x，因为数组为零
 
 End If 
 
结束
 
下一个
 
结束
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

Issue: I'm having to search a large sheet for specific policy numbers. The find function takes quite a while when there are nearly 75,000 rows. Any suggestions on how to compare these two sheets of 75,000 rows? A solution i thought might work would be to sort each sheet and then take the policy number needed to be found and compare it to the middle row. Is there a way to compare that policy number and see if in the simple sort function it would be greater or less than? After finding that comparison, i would reset the upper and lower bounds and find the middle again. ...Would this be quicker? Are there any other suggestions?

Thank you

Current Code:

Sub policyComment()

Dim x As Integer
Dim endRow As Variant
Dim polSer As String
Dim foundVal As String
Dim commentVar As Variant        

Windows("SuspenseNoteMacro.xlsm").Activate
Sheets("Main").Select

Range("A2").Select
endRow = ActiveCell.End(xlDown)

x = 2

Do
    polSer = Range("A" + CStr(x)).Value

    Windows("010713 Suspense ALL.xlsm").Activate
    Sheets("Sheet1").Select

    Set foundRange = Sheets("Sheet1").Cells.Find(what:=polSer, LookIn:=xlFormulas, lookat:=xlWhole)

   'foundRange = ActiveCell.Value     
    If foundRange Is Nothing Then
        Windows("SuspenseNoteMacro.xlsm").Activate
        Sheets("Main").Select
        Range("J" + CStr(x)).Value = "Not Found"
    ElseIf foundRange <> "" Then
        Sheets("Sheet1").Cells.Find(what:=polSer, LookIn:=xlFormulas, lookat:=xlWhole).Activate
        commentVar = Range("J" + CStr(ActiveCell.Row)).Value
        Windows("SuspenseNoteMacro.xlsm").Activate
        Sheets("Main").Select
        Range("J" + CStr(x)).Value = commentVar
    End If

    x = x + 1
    Range("A" + CStr(x)).Select
    foundRange = ""
Loop Until (x = endRow)

End Sub

解决方案

Your code is slow for a few reasons, but mainly because of how you are looping through each cell individually (the actual Find function is not what is slowing it down).

Below, I've put your search column into an array and looped through that, which will be much, much faster. I've also taken out all your select and activate statements, as they are extraneous 99% of the time in VBA, and can also slow down your code a bit. Lastly, I turned off ScreenUpdating which helps as well.

If I missed something in the refactoring, let me know.

Option Explicit

Sub policyComment()

Dim x As Long, endRow As Long, polSer As String, foundRange As range, commentVar As String
Dim varArr() As Variant
Dim wksMain As Worksheet, wks1 As Worksheet

Set wksMain = Sheets("Main")
Set wks1 = Sheets("Sheet1")

Application.ScreenUpdating = False

With wksMain

    endRow = .range("A" & .Rows.Count).End(xlUp).Row
    varArr = .range("A2:A" & endRow)

    For x = LBound(varArr) To UBound(varArr)

        polSer = varArr(x, 1)

        With wks1

            Set foundRange = .Cells.Find(polSer, LookIn:=xlFormulas, lookat:=xlWhole)

            If foundRange Is Nothing Then

                wksMain.range("J" & x + 1).Value = "Not Found" 'need to add 1 to x because arrays are zero based

            Else

                commentVar = .range("J" & foundRange.Row)
                wksMain.range("J" & x + 1).Value = commentVar ''need to add 1 to x because arrays are zero based

            End If

        End With

    Next

End With

Application.ScreenUpdating = True

End Sub

这篇关于更快找到方法和过滤器位置比较？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！