_Excel_RangeFind Error

[JohnyX]

1 hour ago, water said:

Thanks for the feedback! I've already added this to the Excel wiki

Now I  ran into a new challenge , can you please point me in the right direction? 

I am trying to replace more pictures in excel as we discussed here: 

I am trying to use the old image positioning and ratio for the new image, to do that, I search the workbook for a specific string and I get the cell where the string is located. (Because the old image should partially cover that cell where the string is located.) 

Then I use $oShapes and loop through all shapes in the worksheet to find if the shape's upper left corner covers the cell where the string is located, using: 

$oShape.TopLeftCell.Address = "$A$1"

The problem is that the string is in a merged area of cells like A1:A7 and _Excel_RangeFind(string) returns $A$1 because the cells are merged. But the old image's upper left corner covers $A$5 area of the merged cells, so I can't find the correct location of the image to use $oShape.TopLeftCell.Address = "Cell". 

Any ideea what can I do? Can I somehow detect and retrieve the merged area? Or can I use $oShape.TopLeftCell.Address with a range instead of a cell? Or maybe loop through all columns until I find the one with the image? 

If you don't understand what I mean I will came back with a script and a sample file. 

Thank. 

 

[water]

The topleft cell of the merged area like A1:A7 is correctly returned as A1.
If the text seems to cover A5 in this merged area then it is caused by formating the merged area as "centered".
Unfortunately I do not know of a way to determine the exact position of a centered text in a merged area. Sorry.

BTW: A screenshot would be fine

Edited May 21, 2018 by water

[water]

Another idea:
To check if the returned cell is in a merged area use properties
MergeCells https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-mergecells-property-excel and
MergeArea https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-mergearea-property-excel

[JohnyX]

Thank you.

The problem was that i couldn't get the correct cell/position of an image by searching for a text in that cell, because the text is in a merged area(see attached screenshoot).

The search returned A1 but the image's upper left corner is in E1.

Still i managed to get the correct position by looping through all columns, once i found the Row number.

Local $aResult1 = _Excel_RangeFind($oWorkbook, "Text to search for.")
Local $var1 = $aResult1[0][2]

Local $tmp1 = "A,B,C,D,E,F,G,H"
Local $Letters1 = StringSplit($tmp1, ",")
    For $i = 1 To $Letters1[0]
        For $oShape In $oWorkbook.ActiveSheet.Shapes
$img1find = StringReplace($var1, StringMid($var1, 2, 1), $Letters1[$i])
    
            If $oShape.TopLeftCell.Address = $img1find Then

                        $iTop = $oShape.Top
                        $iLeft = $oShape.Left
                        $iWidth = $oShape.Width
                        $iHeight = $oShape.Height
                        $oShape.Delete
                        $oExcel.Worksheets(1).Shapes.AddPicture(@DesktopDir & "\newpic.jpg", False, True, $iLeft, $iTop, $iWidth, $iHeight)
                        ExitLoop
            EndIf
            ExitLoop
        Next

    Next

Thanks for all your help again, the script is fully functional now.

Edited May 21, 2018 by JohnyX

[water]

Great