VBA AddShape

[3rRiXzN0O]

I'm having trouble inserting shapes. I just want to insert a circle centered on a dynamic cell reference. I can get VBA to reference the cells I want, say, J59 and U27, but how do I get from the cell reference to the Top/Left in Points/Characters that the AddShape takes?

 

[shg]

Welcome to the board.

Sub test()
    CircleRange Range("J59:U27")
End Sub
 
Sub CircleRange(r As Range)
    Dim fRad        As Single
    Dim fCtrX       As Single
    Dim fCtrY       As Single
 
    With r.Areas(1)
        fCtrX = .Left + .Width / 2!
        fCtrY = .Top + .Height / 2!
        fRad = Sqr((fCtrX - .Left) ^ 2! + (fCtrY - .Top) ^ 2!)
        .Worksheet.Shapes.AddShape Type:=msoShapeOval, _
                                   Left:=fCtrX - fRad, _
                                   Top:=fCtrY - fRad, _
                                   Width:=2 * fRad, Height:=2 * fRad
    End With
End Sub