Jump to content

Excel Range find function giving bad results


Recommended Posts

Hi all,

I have used _Excel_RangeFind function to find current date from an excel sheet. Dates in my excel sheet is lokks like this "19-04-2015". But the function displays the results like this "20150419000000". Please see the attached image. So i need to use extra code for stripping all those unwanted zeros and put a hyphen between the date value. Why this is happening. Do i need to care anything to prevent this ?.

Here is my code

Local $Output = _Excel_RangeFind($WBook,"19-04-2015","J3:R24") 

And look at the picture, Name of the cell is empty. And address of the cell contained a dollar sign. Can i use this address in another location of this program to find this cell ?

Final question. I need to do some task when a GUI form load event happen. Help file is saying about only these events

1. $GUI_EVENT_MAXIMIZE

2. $GUI_EVENT_RESTORE

3. $GUI_EVENT_MINIMIZE

4. $GUI_EVENT_CLOSE

Where can i found $GUI_EVENT_FORM_LOAD

post-89644-0-56627000-1429437238_thumb.j

Edited by kcvinu
Spoiler

My Contributions

Glance GUI Library - A gui library based on Windows api functions. Written in Nim programming language.

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Link to comment
Share on other sites

The reason is that you are retrieving the 'values' as stored by excel if you want the want the data retrieved to have the same format as displayed in excel you have to retrieve the data as 'text' which returns the data as displayed by excel.

Local $Output = _Excel_RangeFind ( $WBook, "19-04-2015","J3:R24", xlText)

 

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites

@

Bowmore

Error. xlText is undefined function

$xlText is an undeclared global variable. My computer dindn't know this "xlText" 

Spoiler

My Contributions

Glance GUI Library - A gui library based on Windows api functions. Written in Nim programming language.

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Link to comment
Share on other sites

Sorry my mistake.

This slightly modified version of _Excel_RangeFind by water will give you the required information. What I have done is add an extra column to the output containing the excel formatted output in the last column.

; #FUNCTION# ====================================================================================================================
; Author ........: water
; Modified.......: Bowmore
; ===============================================================================================================================
Func _Excel_RangeFindEx($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][7], $iIndex = 0, $iIndexSheets = 1       ;<==== changed [6] to [7]
    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
                $aResult[$iIndex][6] = $oMatch.Text          ;<==== Added .text column
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][7] ;<==== changed [6] to [7]
            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][7] ;<==== changed [6] to [7]
    Return $aResult
EndFunc   ;==>_Excel_RangeFind

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites

@

Bowmore

Thanks. And where did you get this function ?. Please let me know. 

Spoiler

My Contributions

Glance GUI Library - A gui library based on Windows api functions. Written in Nim programming language.

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Link to comment
Share on other sites

@

Bowmore

Thanks. And where did you get this function ?. Please let me know. 

Its just the standard _Excel_RangeFind() function from the Excel.au3 UDF include that I have slightly modified.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...