CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 41

Insert multiple shapes based on cell value

I am trying to insert rectangle shapes x amount of times based on cell input.

Example: cell a1 has 3 entered, make 3 rectangles, if cell a1 has 6 entered, make 6 rectangles.

Ideally, they would be within a parameter but I am not that far yet.

My code changes the dimensions of the rectangle based on cell input.

How can I add this to then reference a different cell to insert x amount of times?

Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

ws.Shapes.AddShape msoShapeRectangle, 50, 50, Range("E11"), Range("F11")

' added a loop but it is not selecting or reading the range correctly

Dim rge As range, cell As range
Set rge = range("A1")
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

ws.Shapes.AddShape msoShapeRectangle, 50, 50, range("E11"), range("F11")

For Each cell In rge

Next cell

End Sub

Upvotes: 1

Views: 552

Answers (1)

BigBen
BigBen

Reputation: 49998

You need a regular For loop, not a For Each:

Dim i As Long

For i = 1 to rge.Value
    ws.Shapes.AddShape msoShapeRectangle, 50, 50, range("E11"), range("F11")
Next

Currently your Range calls are unqualified, i.e. they implicitly reference the ActiveSheet. I assume they should refer to ws.

Some other minor changes:

Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

Dim rge As Range
Set rge = ws.Range("A1")

Dim width As Single
width = ws.Range("E11").Value

Dim height As Single
height = ws.Range("F11").Value

For i = 1 to rge.Value
    ws.Shapes.AddShape msoShapeRectangle, 50, 50, width, height
Next

Upvotes: 3

Related Questions