_Excel_RangeFind problems

[water]

I just started to analyze and get the same strange crash.

Will take me a few days because I'm very busy at the moment. Will let you know as soon as I have a solution.

[water]

I think I solved the problem. It's been a bug.
Can you please test the following function?

expandcollapse popup

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Parent.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][6], $iIndex = 0, $iIndexSheets = 1
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Name.Name
                $aResult[$iIndex][2] = $oMatch.Address
                $aResult[$iIndex][3] = $oMatch.Value
                $aResult[$iIndex][4] = $oMatch.Formula
                $aResult[$iIndex][5] = $oMatch.Comment.Text
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex + 1][6]
    $aResult[0][0] = $iIndex
    $aResult[0][1] = 6
    Return $aResult
EndFunc   ;==>_Excel_RangeFind

[Valnurat]

 

I think I solved the problem. It's been a bug.

Can you please test the following function?

expandcollapse popup

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Parent.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][6], $iIndex = 0, $iIndexSheets = 1
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Name.Name
                $aResult[$iIndex][2] = $oMatch.Address
                $aResult[$iIndex][3] = $oMatch.Value
                $aResult[$iIndex][4] = $oMatch.Formula
                $aResult[$iIndex][5] = $oMatch.Comment.Text
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex + 1][6]
    $aResult[0][0] = $iIndex
    $aResult[0][1] = 6
    Return $aResult
EndFunc   ;==>_Excel_RangeFind

Yes, it working.

This is outcome.

One thing, I'm not sure how I check, if the findings is true or false.

How do I do that?

Because the @error is 0 not matter what I search for.

[water]

If nothing was found the number of rows is 0. Or should the function return an error code? Whill think about it.

If $aSalesResult[0][0] = 0 Then MsgBox(0, "", "Search pattern has not been found!")

Edited June 21, 2013 by water

[Valnurat]

 

If nothing was found the number of rows is 0. Or should the function return an error code? Whill think about it.

If $aSalesResult[0][0] = Then MsgBox(0, "", "Search pattern has not been found!")

Ahh, yes, I see.

well I think it will be best if you keep it standard all the way. If other function is not using @error then you should not do it.

Thank you for your help.

Now I only have another problem and it seems to be a tricky one, but another record has been posted.

[Valnurat]

Small compiler error:

I get this SciTE error, when I compile it:

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /ShowGui /in "C:\Users\profilename\Documents\One4All\One4AllAddUser.au3"
+>14:42:09 Starting AutoIt3Wrapper v.2.1.2.9    Environment(Language:0409  Keyboard:00000406  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)
-> 1 Change(s) made.
>Running AU3Check (1.54.22.0)  from:C:\Program Files (x86)\AutoIt3
C:\Program Files (x86)\AutoIt3\Include\Excel Rewrite.au3(174,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\profilename\Documents\One4All\One4AllAddUser.au3 - 1 error(s), 0 warning(s)
!>14:42:50 AU3Check ended. Press F4 to jump to next error.rc:2
>Exit code: 2    Time: 41.134

[water]

You need to compile it with version 3.3.9.x of AutoIt. Or remove function _Excel_BookAttach.

[Valnurat]

You need to compile it with version 3.3.9.x of AutoIt. Or remove function _Excel_BookAttach.

 

Aha. Thank you.

[water]

I hope the next release will support 3.3.8.1 as well