+ Reply to Thread
Results 1 to 4 of 4

Using excel "find" takes a long time is there a better approach

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Using excel "find" takes a long time is there a better approach

    I have the following code where i am searching for the word "study" and "protocol". But the code i use to find these takes a long time. Is there a better way? Can i reduce / combine some of the finds so it does not take as long. If this helps i expect to find these words somewhere in the first 25 rows of data.


    If ws.Cells(Metric_Rownumber_Col_Study_Number, metric_col).Value <> "NA" Then
    
    MsgBox ("time1")
    
        Set result1 = input_wrksht.Cells.Find(What:="Study", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
        Set result2 = input_wrksht.Cells.Find(What:="Protocol", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    MsgBox ("time2")
        If Not result1 Is Nothing Then
            rowresult = input_wrksht.Cells.Find(What:="study", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            colresult = input_wrksht.Cells.Find(What:="study", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
            If (ws.Cells(Metric_Rownumber_Col_Study_Number, metric_col).Value <> colresult) Then
                chkFile_error = True
                MsgBox ("Check study col number in input file and Necessary_col_file.")
                GoTo end_chk_inputfile
            End If
        ElseIf Not result2 Is Nothing Then
            rowresult = input_wrksht.Cells.Find(What:="protocol", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
            colresult = input_wrksht.Cells.Find(What:="protocol", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
            If (ws.Cells(Metric_Rownumber_Col_Study_Number, metric_col).Value <> colresult) Then
                chkFile_error = True
                MsgBox ("Check study col number in input file and Necessary_col_file.")
                GoTo end_chk_inputfile
            End If
        Else:  chkFile_error = True
                MsgBox ("Could not find study col or protocol number in the input file. ")
                GoTo end_chk_inputfile
        End If
    End If
    Last edited by welchs101; 07-21-2011 at 07:35 AM.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Using excel "find" takes a long time is there a better approach

    Have you tried searching by row rather than columns:
        Set result1 = input_wrksht.Cells.Find(What:="Study", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        Set result2 = input_wrksht.Cells.Find(What:="Protocol", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Re: Using excel "find" takes a long time is there a better approach

    yeah, i tried that.

    What i eventually came up with is this.

    Note: the sheet i was searching at like 96000 rows and about 25 cols.

    What i was searching for "should be" in the first 25 cols and 35 rows.

    So i limited my search to 25cols and 35 rows and ".find" went much much faster.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Using excel "find" takes a long time is there a better approach

    That really shouldn't matter if you search by rows, unless the search value isn't found.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1