issue with _Excel_RangeFind ?

[GreenCan]

I am trying to find a way to _Excel_RangeFind a cell format but I don't succeed in finding a proper way.

Please run the example script first, columns B and C are relevant for the test case. Disregard columns D to G.

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>


Global $sTimeLocale = RegRead("HKCU\Control Panel\International", "sShortTime")
ConsoleWrite("Locale time format on this PC: " & $sTimeLocale & @CRLF)


Global $oExcel = _Excel_Open();$bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bInteractive = Default, $bForceNew = Default)
Global $oWorkbook = _Excel_BookNew($oExcel)


; ***************************************************************
; Example - Format Numbers
; *****************************************************************
Global $sTimeformat = "[u]:mm"
Global $aFormatExamples[1][7] = [["Format Examples", $sTimeformat, $sTimeformat, "###0,00", "$#.##0,00", "€ #.##0,00;[Red]€ -#.##0,00", "General"]];Array to Create Headers, European format example
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aFormatExamples, "A1")

; Fill-up some cells with random Numbers
Global $iRows= 15
Global $aArray2D[$iRows +6][UBound($aFormatExamples, 2) + 1]

For $i = 4 To $iRows - 2
    $aArray2D[$i][0] = Random(0, 1000)
Next

For $i = 1 To $iRows + 5
    $aArray2D[$i][1] = Random(0, 1000)
Next
For $j = 2 To UBound($aFormatExamples, 2) - 2
    For $i = 2 To $iRows + 5
        $aArray2D[$i][$j] = Random(-1000, 1000);this time allow negative numbers
    Next
Next
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aArray2D, "B2")

MsgBox(0, "Formating example", "Applying format in the green area only")

$oExcel.Activesheet.Range("B4:G16").Interior.Color = 0x3AFF00 ; color range green

; Set each column to its type of Format
$oExcel.ActiveSheet.Range("B4:B15").NumberFormat = $aFormatExamples[0][1]
$oExcel.ActiveSheet.Range("C4:C15").NumberFormat = $aFormatExamples[0][2]
$oExcel.ActiveSheet.Range("D4:D15").NumberFormat = $aFormatExamples[0][3]
$oExcel.ActiveSheet.Range("E4:E15").NumberFormat = $aFormatExamples[0][4]
$oExcel.ActiveSheet.Range("F4:F15").NumberFormat = $aFormatExamples[0][5]
$oExcel.ActiveSheet.Range("G4:G15").NumberFormat = $aFormatExamples[0][6]

;AutoFits
$oExcel.Columns.AutoFit
$oExcel.Rows.AutoFit

MsgBox(0, "Formating example", "Set sums")
Global $aSums[1][7] = [["Range starting  Row 3","=SUM(B3:B23)", "=SUM(C3:C23)", "=SUM(D3:D23)", "=SUM(E3:E23)", "=SUM(F3:F23)", "=SUM(G3:G23)"]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aSums, "A24")

Global $aSums[1][7] = [["range starting  Row 4","=SUM(B4:B23)", "=SUM(C4:C23)", "=SUM(D3:D23)", "=SUM(E3:E23)", "=SUM(F3:F23)", "=SUM(G3:G23)"]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aSums, "A25")

;AutoFits
$oExcel.Columns.AutoFit
$oExcel.Rows.AutoFit


MsgBox(0, "test", "RangeFind format?")

; full match
$aResult =_Excel_RangeFind($oWorkbook, $sTimeformat, "B3:B23", $xlFormulas, $xlPart); , $iLookAt = Default, $bMatchcase = Default)
ConsoleWrite(@ScriptLineNumber & " " & @error & @CR)
_ArrayDisplay($aResult, @ScriptLineNumber)
; partial match
$aResult =_Excel_RangeFind($oWorkbook, ":mm", "B3:B23", $xlFormulas, $xlPart); , $iLookAt = Default, $bMatchcase = Default)
ConsoleWrite(@ScriptLineNumber & " " & @error & @CR)
_ArrayDisplay($aResult, @ScriptLineNumber)

If MsgBox(4 + 0, "Formating example", "Done, close without save ") = 6 Then
    _Excel_BookClose($oWorkbook, False)
     _Excel_Close($oExcel)
EndIf

The format is put to ':mm' in the green area only
Please check if this format is appropriate for your system, might need to change this to '[h]:mm' as pr your Locale Excel settings (line 16)

Case 1: when summing up the columns as from row 3, Excel considers the sum for the range B3:B23/C3:C23 to be in format 'General'
Case 2: when summing up the columns as from row 4, Excel considers the sum for the range B4:B23/C4:C23  to be in format ':mm'

This is an expected behavior of Excel, although one might think that at least for column B, there is no ambiguity, the sum should be in :mm, but OK...

I now want to search for the column range if I can find cells in :mm format, and according to the result, I can reformat the sum in the appropriate number format.

In VB:

Sub FindNumberFormat()
'
'  FindNumberFormat
'  run multiple times to find the next cell in the format...
'
    Application.FindFormat.NumberFormat = "[h]:mm"
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
End Sub

In AutoIt however, nothing is found and

$aResult =_Excel_RangeFind($oWorkbook, $sTimeformat, "B3:B23", $xlFormulas, $xlPart)

returns an empty array.

I am confused with the VB statement 'Application.FindFormat.NumberFormat = "[h]:mm"' that I find nowhere in the  _Excel_RangeFind() function.

Has anyone tested the function already?

Thank you (...water )

PS. I mostly try to find a solution myself, but sometimes, I have to look at the real specialists...

Edited April 20, 2015 by GreenCan

[water]

Hi Greencan,

Will have a look at it as soon as I return from vacation

[GreenCan]

Hi Greencan,

Will have a look at it as soon as I return from vacation

​Water, your vacation must have been pretty long...

[water]

Opps,
looks like I missed to solve this problem. Will have a look at it the next days when the weather gets bad so I do not have to sit on my racing bike

[GreenCan]

Don't worry. I will be on my bike the whole next week, touring around in France, scheduled 1.000 km at tourist speed...

Cheers

[water]

Have a nice week and and enjoy France. I wish you perfect weather for biking

[water]

I had a quick look at the problem.
Result: _Excel_RangeFind does not support looking for formats at the moment.

Solving the current problem shouldn't be too hard. It will take some time but I hope to come up with a solution quite soon ...

[water]

This example works for me (for red cells):

#include <Excel.au3>

; Create workbook with 3 rows and 3 columns. Set cells A1 and A3 to red
Global $aUsedRow, $sMaxUsedColumn
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Global $aValues = [[11, 21, 31], [12, 22, 32], [13, 23, 33]]
_Excel_RangeWrite($oWorkbook, 1, $aValues)
Global $iRed = 234 ; Hex 0xEA
$oWorkbook.Activesheet.Range("A1").Interior.Color = $iRed
$oWorkbook.Activesheet.Range("A3").Interior.Color = $iRed

; Get the max used range of the active sheet
$sMaxUsedColumn = _Excel_ColumnToLetter($oWorkbook.ActiveSheet.Usedrange.Columns.Count)
; Loop through all rows and check column A for a red cell. If found, read all used cells of this row and display the array.
For $i = 1 To $oWorkbook.ActiveSheet.Usedrange.Rows.Count
    If $oWorkbook.Activesheet.Range("A" & $i).Interior.Color = $iRed Then
        $aUsedRow = _Excel_RangeRead($oWorkbook, $oWorkbook.Activesheet, $oWorkbook.Activesheet.Range("A" & $i & ":" & $sMaxUsedColumn & $i))
        _ArrayDisplay($ausedRow)
    EndIf
Next