How do I use a For Each Loop to review information on one sheet and build shapes to another sheet?

Question

Please let me explain what I wish to do:

(1) I have created an Excel workbook whereby a person can write individual steps of a process. Let's say it is written on a worksheet called "Process A". In a cell to the right of each step, the person can specify if the step requires a circle (for connectors), a diamond (for decisions) or a rectangle (for a process step).

(2) For each step in the sheet called "Process A", I also have conditional formulas, indicating where I want the shape to be placed (Left, Top, Width, Height) on another sheet (let's call it "Flowchart A"). In case you want to try this, I am listing the coordinates as follows: LEFT > Connectors = 147; Processes and Decisions = 49

WIDTH > Connectors = 90; Processes and Decisions = 286

HEIGHT of a shape > Connectors = 90; Processes and Decisions = 210

HEIGHT of an arrow > All arrows = 60

TOP > (A calculation based on the top of the previous shape + height of previous shape + length of an arrow).For example, if shape 1 is a connector and shape 2 is a process, then shape 2 starts at 150 (assuming the top of shape 1 starts at 0... [0 + 90 + 60]

The following code does produce all of the shapes, but it stacks them all at Top Point Zero (0).

Sub TestRun()

Dim ws1 As Worksheet
Set ws1 = Worksheets("Process A")

Dim LDS As Shape

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


Dim LEFT As Integer
Dim TOP As Integer
Dim WIDTH As Integer
Dim HEIGHT As Integer

Dim LRange As Range
For Each LRange In ws1.Range("T2:T23")
LEFT = LRange.Value
Next LRange

Dim TRange As Range
For Each TRange In ws1.Range("U2:U23")
TOP = TRange.Value
Next TRange

Dim WRange As Range
For Each WRange In ws1.Range("V2:V23")
WIDTH = WRange.Value
Next WRange

Dim HRange As Range
For Each HRange In ws1.Range("W2:W23")
HEIGHT = HRange.Value
Next HRange

Dim ShpRange As Range
For Each ShpRange In ws1.Range("D2:D23")
If ShpRange.Value = "Connector" Then
Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartConnector, LEFT, TOP, WIDTH, HEIGHT)

ElseIf ShpRange.Value = "Process" Then
Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartProcess, LEFT, TOP, WIDTH, HEIGHT)

ElseIf ShpRange.Value = "Decision" Then
Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartDecision, LEFT, TOP, WIDTH, HEIGHT)

End If


Next ShpRange

End Sub

I tried the following variations in the placement of coding:

(1) List "For Each" Statements with their conditions. Then list all of the "Next Statements". (2) List all the "For Each" Statements together, followed by all condition statements listed together, followed by all "Next Statements" listed together.

When I ran the macro after these edits, I received a "Compile Error: Invalid Next Control Variable Reference".

I am new to VBA and have tried many things to do the work, but I feel like I'm running into a brick wall over and over again. I would greatly appreciate your help! Thank you in advance!

Answer 1

You can simplify this down to one For Each loop, using Offset to refer to different columns relative to the range you loop through.

For example, this code loops through T2:T23, and then refers to Columns U-W with a positive offset, and Column D with a negative.

Using a Select Case statement simplifies the choice of a Connector, Process, or Decision.

Option Explicit

Sub TestRun()
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Process A")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim LDS As Shape
    Dim Left As Integer, Top As Integer, Width As Integer, Height As Integer
    Dim rng As Range

    For Each rng In ws1.Range("T2:T23")
        Left = rng.Value
        Top = rng.Offset(, 1).Value
        Width = rng.Offset(, 2).Value
        Height = rng.Offset(, 3).Value

        Select Case rng.Offset(, -16).Value
            Case "Connector"
                Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartConnector, Left, Top, Width, Height)
            Case "Process"
                Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartProcess, Left, Top, Width, Height)
            Case "Decision"
                Set LDS = ws2.Shapes.AddShape(msoShapeFlowchartDecision, Left, Top, Width, Height)
        End Select
    Next rng

End Sub