Check to find if there is a picture above a certain cell

[treygon]

I am pasting shapes to a specific cell range. However, I first want to find out if there is already a shape in (on top) the cell range. If there is already a shape, I need to identify it and delete it so I can paste a new shape there. IF there is no shape located in the range, then to go ahead and past a shape there.

For example,

Sheets(Sheet1).Range("A1") is where I am pasting shapes.

I want to first identify if there is already a picture located at Range("A1"). If there is, delete it, and then paste the new picture.

I am using the following code to paste the new shape:

    Highlight_Left_Ref = Worksheets("Client Report").Range("HighlightRef").Left - 5.25
    Highlight_Top_Ref = Worksheets("Client Report").Range("HighlightRef").Top - 3
      
    Sheets("StateHighlights").Select
    Sheets("StateHighlights").Shapes("52One").Copy
    Application.Goto Sheets("Client Report").Range("HighlightRef")
    ActiveSheet.Paste
    Sheets("Client Report").Shapes("52One").Left = Highlight_Left_Ref
    Sheets("Client Report").Shapes("52One").Top = Highlight_Top_Ref

Using Excel 2007

 

[Domenic]

Maybe something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] Shp [color=darkblue]As[/color] Shape
    [color=darkblue]Dim[/color] MyShape [color=darkblue]As[/color] Shape
    [color=darkblue]Dim[/color] MyRange [color=darkblue]As[/color] Range

    [color=darkblue]Set[/color] MyShape = Sheets("State Highlights").Shapes("52One")
    
    [color=darkblue]Set[/color] MyRange = Sheets("Client Report").Range("A1")
    
    [color=darkblue]With[/color] Sheets("Client Report")
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Shp [color=darkblue]In[/color] .Shapes
            [color=darkblue]If[/color] Shp.TopLeftCell.Address = MyRange.Address [color=darkblue]Then[/color]
                Shp.Delete
                MyShape.Copy
                MyRange.PasteSpecial
                [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] Shp
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    MyShape.Copy
    MyRange.PasteSpecial

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]