CODEWITHSUNDEEP
vbaexcel
user1513202
user1513202

Reputation: 51

Skipping to the next cell in VBA in a for loop

I'm writing a macro that basically draws a circle for every value in a column with a size based on that value, however some of the cells are blank and I just need to skip over them. I run into an error when I hit the first blank cell. Here's the code I have so far: 

Sub plotCircles()
    Set R = Range("D7:D205")
    For Each Value In R
        If Value = "" Then
            Value = Value + 1
        Else
            Set shp = ActiveSheet.Shapes.AddShape(msoShapeOval, 10, 10, Value, Value)
        End If
    Next Value
End Sub

Upvotes: 1

Views: 1450

Answers (1)

L42
L42

Reputation: 19737

Try this:

Sub PlotCircles()
    Dim r As Range, c As Range, shp As Shape
    Set r = Sheet1.Range("D7:D205") 'change to suit
    For Each c In r
        With c
            If .Value <> "" Then
                Set shp = Sheet1.Shapes.AddShape _
                    (msoShapeOval, .Left, .Top, .Value, .Value)
            Else
                .Value = .Value + 1
            End If
        End With
    Next
End Sub

I don't know why you hardcode your Left and Top argument for the AddShape method.
That will draw all the circles in the same location.
Above however draws the circles in the cell where you get your values from.
You can adjust that to suit your needs. HTH.

Upvotes: 1

Related Questions