CODEWITHSUNDEEP
excelvba
LyessD
LyessD

Reputation: 221

Error 1004 VBA : How to set text of shapes that are in range of cells

I want to set text of shapes that are in range of cells.
In the below code I loop through shapes in my activesheet, if the range given masqueA intersect with topleftcell then select this shape and set text of this selection.

Sub numShape()

Dim masqueA As Range
Set masqueA = Range("b33:l42")
cpt = 1

For Each shapeTemp In ActiveSheet.Shapes
    If Not Intersect(Range("masqueA"), shapeTemp.TopLeftCell) Is Nothing Then
        shapeTemp.Select
        Selection.TextFrame.Characters.Text = "cpt"
        cpt = cpt + 1
    End If
Next shapeTemp

End Sub

However I have this error : Run-Time error'1004' : Application-defined or object-defined error

Does someone can explain to me why I have this error and how to fix it. By the way is my code doing what I trying to do ?

Thanks!

Upvotes: 0

Views: 390

Answers (1)

TinMan
TinMan

Reputation: 7759

masqueA is a Range and not a Defined Name. If you want to refer to a range by name then use Range("b33:l42").Name = "masqueA" and Range("masqueA") as is just use Intersect(masqueA, shapeTemp.TopLeftCell). 

Sub numShape()

    Dim masqueA As Range
    Set masqueA = Range("b33:l42")
    cpt = 1

    For Each shapeTemp In ActiveSheet.Shapes
        If Not Intersect(masqueA, shapeTemp.TopLeftCell) Is Nothing Then
            shapeTemp.TextFrame.Characters.Text = "cpt"
            cpt = cpt + 1
        End If
    Next shapeTemp

End Sub

Upvotes: 1

Related Questions