2

Have a sheet with a list of Cell references in two columns.

Trying to create a macro that pulls these into a range and uses the first cell in column A for the start point of an autoshape line and the second cell in column B as the end point of an autoshape line.

The script is working and doing what I want it to however at the end of execution I am getting "Subscript out of range error"

What am I doing wrong?

rng = Range("A1:B100")

Worksheets("Map").Activate

For Each row In rng

    i = i + 1

    ActiveSheet.Shapes.AddConnector(msoConnectorStraight, Range(rng(i, 1)).Left, Range(rng(i, 1)).Top, Range(rng(i, 2)).Left, Range(rng(i, 2)).Top).Select

Next row
Vityata
  • 42,633
  • 8
  • 55
  • 100
Ries
  • 35
  • 3

2 Answers2

1

The Range("A1:B100") has no connection to Worksheets("Map") beyond a possible coincidence that Worksheets("Map") was the active worksheet. Provide proper parent worksheet reference.

You Set objects like ranges to their vars.

Don't Select the connectors you create; not in a loop, not ever.

with Worksheets("Map")

    set rng = .Range("A1:B100")

    For Each row In rng

        i = i + 1

        .Shapes.AddConnector msoConnectorStraight, _
                             .Range(rng(i, 1)).Left, .Range(rng(i, 1)).Top, _
                             .Range(rng(i, 2)).Left, .Range(rng(i, 2)).Top

    Next row

end with
  • 1
    @Vityata, of course! Good catch. For some reason I was thinking that I was setting an object to the newly added connector. –  Sep 20 '18 at 08:04
1

Avoid select and activate, declare all the variables and loop only from the rows of the range:

Sub TestMe()

    Dim rng As Range
    Set rng = Worksheets("Map").Range("A1:B100")

    Dim row As Range
    Dim i As Long

    For Each row In rng.Rows
        i = i + 1
        Worksheets("Map").Shapes.AddConnector msoConnectorStraight, _
            row.Cells(i, 1).Left, _
            row.Cells(i, 1).Top, _
            row.Cells(i, 2).Left, _
            row.Cells(i, 2).Top
    Next row

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100