_Excel_Rangefind issue - (Moved)

[baamr]

Hi,

 

Case

I need to find excact value of the first cell with D3.

In my search it returns the cell($AK$24) including D33 cause this includes D3.

How can I go around this  so it only puts out the “real”value of the first cell including D3

In this case it should be $AK$25

 

 

Edited February 15, 2023 by baamr
removed one pic

[ioa747]

then search for 'D3,'  since the ', '  is there

or put a space after each value D3 , D33 , D22

and then search for 'D3 '

Edited February 15, 2023 by ioa747

[water]

Or process the array the search for D3 returns, stringsplit the value cell and check the returned array for correct values.
Can provide an example if needed.

[Melba23]

Moved to the appropriate AutoIt General Help and Support forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[mistersquirrle]

I would suggest looking at StringSplit: https://www.autoitscript.com/autoit3/docs/functions/StringSplit.htm

Like water said, iterate over the array checking the 'Value' column by using StringSplit (with both space and comma as your delimiters). You can then check each value separately. Something like this:
 

#include <StringConstants.au3>
Local Enum $eHeader_Sheet, $eHeader_Name, $eHeader_Cell, $eHeader_Value, $eHeader_Formula, $eHeader_Comment, _
        $eHeader_Max
Local $aArray[][$eHeader_Max] = [ _
        ['Sheet0', '', '$AK$24', 'D1, D2, D33, D36', 'D1, D2, D33, D36', ''], _
        ['Sheet0', '', '$AK$25', 'D3, D99', 'D3, D99', ''], _
        ['Sheet0', '', '$AZ$25', 'D3, D99', 'D3, D99', ''], _
        ['Sheet0', '', '$AK$32', 'D27, D28, D29, D30, D31', 'D27, D28, D29, D30, D31', ''], _
        ['Sheet0', '', '$AK$61', 'LED1, LED2, LED3, LED4, LED5', 'LED1, LED2, LED3, LED4, LED5', ''] _
        ]
Local $aSplit
Local $sCellToSearchFor = 'D3'
Local $bFound = False

For $iRow = 0 To UBound($aArray) - 1
    $bFound = False
    $aSplit = StringSplit($aArray[$iRow][$eHeader_Value], ' ,', $STR_NOCOUNT)
    For $iValue = 0 To UBound($aSplit) - 1
        If StringCompare(StringStripWS($aSplit[$iValue], $STR_STRIPALL), $sCellToSearchFor) = 0 Then
            ConsoleWrite(@TAB & 'Found ' & $sCellToSearchFor & ' reference in row ' & $iRow & ': ' & $aArray[$iRow][$eHeader_Value] & ', Cell: ' & $aArray[$iRow][$eHeader_Cell] & @CRLF)
            $bFound = True
            ExitLoop
        EndIf
    Next

    If Not $bFound Then
        ConsoleWrite('Not found in row: ' & $iRow & @CRLF)
    EndIf
Next

Exit

I'm using StringCompare instead of just " = " because it can be a bit safer occasionally when comparing if strings are equal. Also, StringStripWS to remove any whitespace when doing the comparison, just in case there's extra spaces. If you wanted to stop searching after the first match just do ExitLoop 2, and it'll exit both For loops.

Edited February 16, 2023 by mistersquirrle
Updated code

[baamr]

15 hours ago, ioa747 said:

then search for 'D3,'  since the ', '  is there

or put a space after each value D3 , D33 , D22

and then search for 'D3 '

It could work but some places in sheet do not have commas, only one value. Commas occurs only when there is more than one value in cell.

[baamr]

3 hours ago, mistersquirrle said:

I would suggest looking at StringSplit: https://www.autoitscript.com/autoit3/docs/functions/StringSplit.htm

Like water said, iterate over the array checking the 'Value' column by using StringSplit (with both space and comma as your delimiters). You can then check each value separately. Something like this:
 

#include <StringConstants.au3>
Local Enum $eHeader_Sheet, $eHeader_Name, $eHeader_Cell, $eHeader_Value, $eHeader_Formula, $eHeader_Comment, _
        $eHeader_Max
Local $aArray[][$eHeader_Max] = [ _
        ['Sheet0', '', '$AK$24', 'D1, D2, D33, D36', 'D1, D2, D33, D36', ''], _
        ['Sheet0', '', '$AK$25', 'D3, D99', 'D3, D99', ''], _
        ['Sheet0', '', '$AZ$25', 'D3, D99', 'D3, D99', ''] _
        ]
Local $aSplit
Local $sCellToSearchFor = 'D3'

For $iRow = 0 To UBound($aArray) - 1
    $aSplit = StringSplit($aArray[$iRow][$eHeader_Value], ' ,', $STR_NOCOUNT)
    For $iValue = 0 To UBound($aSplit) - 1
        If StringCompare(StringStripWS($aSplit[$iValue], $STR_STRIPALL), $sCellToSearchFor) = 0 Then
            ConsoleWrite(@TAB & 'Found ' & $sCellToSearchFor & ' reference in row ' & $iRow & ': ' & $aArray[$iRow][$eHeader_Value] & ', Cell: ' & $aArray[$iRow][$eHeader_Cell] & @CRLF)
            ExitLoop
        EndIf
    Next
    ConsoleWrite('Not found in row: ' & $iRow & @CRLF)
Next

Exit

I'm using StringCompare instead of just " = " because it can be a bit safer occasionally when comparing if strings are equal. Also, StringStripWS to remove any whitespace when doing the comparison, just in case there's extra spaces. If you wanted to stop searching after the first match just do ExitLoop 2, and it'll exit both For loops.

Unfortunately it does not work. This example outputs the same fault as originally. It gives result in every row. My only result should be in row 1 were D3 occurs first.

[mistersquirrle]

8 minutes ago, baamr said:

Unfortunately it does not work. This example outputs the same fault as originally. It gives result in every row. My only result should be in row 1 were D3 occurs first.

Could you post what the output is? As mentioned in my post, if you want to stop at the first found instance, use ExitLoop 2, or better yet, put my example loops in a function and use Return to return the row or cell that has the first match.

Here's what I get for output with my example (updated to include all the rows in your example):

Not found in row: 0
    Found D3 reference in row 1: D3, D99, Cell: $AK$25
    Found D3 reference in row 2: D3, D99, Cell: $AZ$25
Not found in row: 3
Not found in row: 4

 

If it's not working, then please make an example or post your code so that we can run it and see why it doesn't work. Just saying that it doesn't work without more information isn't helpful for us to give you a solution.

Edited February 16, 2023 by mistersquirrle

[baamr]

29 minutes ago, mistersquirrle said:

Could you post what the output is? As mentioned in my post, if you want to stop at the first found instance, use ExitLoop 2, or better yet, put my example loops in a function and use Return to return the row or cell that has the first match.

Here's what I get for output with my example (updated to include all the rows in your example):

Not found in row: 0
    Found D3 reference in row 1: D3, D99, Cell: $AK$25
    Found D3 reference in row 2: D3, D99, Cell: $AZ$25
Not found in row: 3
Not found in row: 4

 

If it's not working, then please make an example or post your code so that we can run it and see why it doesn't work. Just saying that it doesn't work without more information isn't helpful for us to give you a solution.

Sorry, my fault it works. I just need to use the first line. Thanks,