_Excel_RangeFind() and empty search

[MWIProd]

Hi,

In help file, function return @error = 2 if $sSearch is empty.

So If I want to search empty cell, I can't use AutoIt functions and I must check all sheet cells values ?

[ioa747]

 

#include <Array.au3>

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object

;Example data *****************************
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.Add ; Add a new workbook
Local $aArray[16][16]
For $i = 0 To 15
    For $j = 0 To 15
        $aArray[$i][$j] = (Random(0, 10, 1) > 0) ? (Random(100, 1000, 1)) : ("")
    Next
Next
;~ _ArrayDisplay($aArray)
$oExcel.activesheet.range("A1:O16").value = $aArray ; Fill cells with example numbers
Sleep(2000) ; Wait a while before continuing
;******************************************

Local $aEmpty = [0]
For $iCell In $oExcel.ActiveSheet.Range("A1:O16")
    If $iCell.Value = "" Then
        ReDim $aEmpty[UBound($aEmpty) + 1]
        $aEmpty[0] += 1
        $aEmpty[$aEmpty[0]] = $iCell.Address
    EndIf
Next
_ArrayDisplay($aEmpty)

 

[Nine]

A bit of a different approach :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
Local $oRange = $oWorkbook.Sheets(1).Range("A1:K7")
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)
ConsoleWrite($oList.count & " empty cells found" & @CRLF)
For $oCell In $oList
  ConsoleWrite($oCell.Address & @CRLF)
Next
$oList.value = "<<Empty>>"

 

[water]

More special ranges can be found in the wiki.

[MWIProd]

Thank you all for your answers.

I tried Nine code but it doesn't work for me.

In fact my sheet contains fusionned cells (col E & F).

So If I want to search empty cells in E:F, results are ALL cells in E:F 😞

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
Local $oRange = $oWorkbook.Sheets(1).Range("E:F")
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)
ConsoleWrite($oList.count & " empty cells found" & @CRLF)
For $oCell In $oList
  ConsoleWrite($oCell.Address & @CRLF)
Next

Results :

$E$1
$F$1
$E$2
$F$2
$E$3
$F$3
$E$4
$F$4
$E$5
$F$5
$E$6
$F$6
$E$7
$F$7
$E$8
$F$8
$E$9
$F$9
$E$10
$F$10

Edited March 18 by MWIProd

[Jos]

Your range definition is not correct and requires a row number for both columns:

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
Local $oRange = $oWorkbook.Sheets(1).Range("E1:F20")
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)
ConsoleWrite($oList.count & " empty cells found" & @CRLF)
For $oCell In $oList
  ConsoleWrite($oCell.Address & @CRLF)
Next
_Excel_BookClose($oWorkbook)

4 empty cells found
$E$6
$E$9
$F$9
$F$13

 

Edited March 18 by Jos

[MWIProd]

The cells from col E and F are merged.

I am sorry, I used word "fusionned". 😞

So with this code (and without row number) I can list all cell with string 'zzzzzzzz' :

Local $aResult = _Excel_RangeFind($oWorkbook, 'zzzzzzzz', 'E:F', Default, $xlWhole)

With your code, all merged cells are listed.

If I merge cells A1, B1 and C1, $A$1, $B$1 and $C$1 are listed.

[Nine]

You may need to unmerge the "fusionned" cells before :

...
Local $oRange = $oWorkbook.Sheets(1).Range("E:F")
$oRange.UnMerge()
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)
...

untested (too old ms-excel)

[donnyh13]

Technically Column F is empty, only Column E contains the data. Try one column at a time. And skip the second column of merged cells.

Quote

2 empty cells found
$E$5
$F$5

 

Local $oRange = $oWorkbook.Sheets(1).Range("E:E")
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)

 

Edited March 18 by donnyh13

[MWIProd]

It works !

Many thanks to all of you. 🙂

Local $oRange = $oWorkbook.Sheets(1).Range("E:F")
$oRange.UnMerge()
$oRange = $oWorkbook.Sheets(1).Range("E:E")
Local $oList = $oRange.SpecialCells($xlCellTypeBlanks)
ConsoleWrite($oList.count & " empty cells found" & @CRLF)
For $oCell In $oList
  ConsoleWrite($oCell.Address & @CRLF)
Next