Add Shape to grid with co-ordinates error

[Nadine67]

Thank you for any attention my post may receive.

So I have the code below to add shapes to a grid with given coordinates however I get RT Error 13 - type mismatch and have no idea why. even though the error appears, the shapes still appear on the grid.

The debug identifies the error at line

L = C.Offset(0, -25) * (.Width)

in my code below.

I have no idea why the shapes still appear even though the error occurs. I would appreciate any help and suggestion to correct this.

Have a great day!

Sub myMap()
'    Const Dia   As Single = 15 'Diameter of Permit Markers
    Dim sh      As Shape
    Dim C       As Range
    Dim L       As Single
    Dim T       As Single
    Dim W       As Single
    Dim H       As Single
    Dim r As Range
    Set r = Range("A3:AQ26")
    W = r.Width
    H = r.Height
    With Workbooks("Commissioning Database_Working_3.xlsm")
        For Each sh In Sheets("Permit Map").Shapes
            If sh.Name <> "myMap" Then sh.Delete
        Next
   'When permits are activated, a green dot will be displayed on the Permit Map at the coresponding coordinate
        For Each C In .Sheets("Active Permits").Range("AB12:AB600")
            If C = "G" Then
            With Sheets("Permit Map").Shapes("myMap")
                L = C.Offset(0, -25) * (.Width)
                T = C.Offset(0, -24) * (.Height)
            End With
        With Sheets("Permit Map").Shapes.AddShape(msoShapeOval, L, T, 10, 10)
                    .Fill.ForeColor.RGB = RGB(0, 204, 0)
                    .Line.ForeColor.RGB = RGB(0, 204, 0)
                    .Line.Weight = 5
                End With
      'When permits require revalidating, the dot will become amber if 'Validation' time is within 12hrs due
          ElseIf C = "A" Then
            With Sheets("Permit Map").Shapes("myMap")
                L = C.Offset(0, -25) * (.Width)
                T = C.Offset(0, -24) * (.Height)
                End With
          With Sheets("Permit Map").Shapes.AddShape(msoShapeOval, L, T, 15, 15)
                    .Fill.ForeColor.RGB = RGB(255, 192, 0)
                    .Line.ForeColor.RGB = RGB(255, 192, 0)
                    .Line.Weight = 5
                End With
      'When permits require revalidating, the dot will become red if 'Validation' time is within 8hrs due
          ElseIf C = "R" Then
            With Sheets("Permit Map").Shapes("myMap")
                L = C.Offset(0, -25) * (.Width)
                T = C.Offset(0, -24) * (.Height)
                End With
          With Sheets("Permit Map").Shapes.AddShape(msoShapeOval, L, T, 15, 15)
                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                    .Line.ForeColor.RGB = RGB(255, 0, 0)
                    .Line.Weight = 5
                End With
 '       'When permits require monitoring, the dot will become yellow
 '          ElseIf C = "Monitor" Then
 '           With Sheets("Permit Map").Shapes("myMap")
 '               L = C.Offset(0, -14) * (.Width)
 '               T = C.Offset(0, -13) * (.Height)
 '            End With
 '              With Sheets("Permit Map").Shapes.AddShape(msoShapeOval, L, T, Dia, Dia)
 '                   .Fill.ForeColor.RGB = RGB(253, 170, 3)
 '                   .Line.ForeColor.RGB = RGB(253, 170, 3)
 '                  .Line.Weight = 5
 '                  End With
            End If
        Next
    End With
End Sub

 

[James006]

Hello Nadine,

As an initial test ... would don't you declare your variables As Double ... instead of ... what they currently show As Single ...

HTH

 

[Nadine67]

Thank you for your suggestion James. I have declared the variables as Double. I am still getting the error though.

James what is the difference between Double and Single? Why wouldn't I declare the co-ordinates as integers?

Nadine

 

[Nadine67]

Any suggestions would be greatly appreciated.