Modify

Opened 3 years ago

Closed 3 years ago

#2993 closed Bug (Fixed)

_Excel_RangeFind returns only last occurrence of string found Rather than ALL

Reported by: ricrat51@… Owned by: water
Milestone: 3.3.13.21 Component: Standard UDFs
Version: 3.3.12.0 Severity: None
Keywords: Cc:

Description

It appears that the routine returns only the last occurrence of string rather than all occurrences
Modified as follows and it appear to work
Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)

If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" 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
Local $aResult[100][6], $iIndex = -1, $iIndexSheets = 1;;;; FIXED by RIC
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;;;; FIXED by RIC
$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
;;;;;$iIndex = $iIndex + 1;;;; FIXED by RIC
If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]

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]
Return $aResult

EndFunc ;==>_Excel_RangeFind

Attachments (0)

Change History (4)

comment:1 Changed 3 years ago by water

  • Component changed from AutoIt to Standard UDFs

comment:2 Changed 3 years ago by water

The problem was caused by exiting the loop to search in the next sheet before increasing the index. Thus the first hit of the next sheet was overwriting the last hit of the previous sheet.
This should work as well. Can you please confirm?

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
	If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" 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
				$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
				$iIndex = $iIndex + 1 ; <== increasing index before exiting the loop
				If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
				$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][6]
	Return $aResult
EndFunc   ;==>_Excel_RangeFind

comment:3 Changed 3 years ago by water

  • Owner set to water
  • Status changed from new to accepted

comment:4 Changed 3 years ago by water

  • Milestone set to 3.3.13.21
  • Resolution set to Fixed
  • Status changed from accepted to closed

Fixed by revision [11251] in version: 3.3.13.21

Guidelines for posting comments:

  • You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
  • In-depth discussions should take place on the forum.

For more information see the full version of the ticket guidelines here.

Add Comment

Modify Ticket

Action
as closed The owner will remain water.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.