Any way to capture x,y coordinates of a given cell?

[clownfish]

Hi all-
I am trying to write some VBA that will allow me to draw an autoshape arrow linking one cell to another using...
.AddLine([begin x], [begin y], [end x], [end y])

I have a grid of cells that covers the array B2:BI41 (40 rows X 60 columns) that is empty except for 7 cells which have a letter in them (a, b, c d, e, f, or g). These cells will move all around the grid. I currently have x and y "axes" along the top and left side of this grid, respectively, but they are not very useful-- simply 1 2 3 4 5 .... -- I can obviously not use these as x , y coordinates for a line.

What I would like is EITHER some way to dynamically obtain the x-y coordinates for a given cell OR dynamically create x and y axis values which I could in turn use in the .AddLine function above. By dynamically (this is the key here!), I mean that even if column widths/row heights are changed, I will still get accurate x-y coordinates for the center of that cell (or at least a good approximation).

So, for instance if "b" is in cell F19 and "e" is in cell AG38 and I want to draw an arrow from "b" to "e".... ?

Hope this makes sense! I am a complete caveman with VBA, by the way. Thanks a lot.

NOTE that I am NOT making a chart-- this is literally a grid of cells (basically, a map)

[solnajeff]

Hi

Whilst I am not sure this is exactly what you need I hope it will give you an idea.

Regards

Jeff

[shg]

This might help you get started:

Sub DrawArrow(r1 As Range, r2 As Range)
    ' shg 2008-0803
    ' Draws a line beween the center of the two ranges
    
    Dim x1 As Double
    Dim x2 As Double
    Dim y1 As Double
    Dim y2 As Double
    
    With r1
        x1 = .Left + .Width / 2
        y1 = .Top + .Height / 2
    End With
    
    With r2
        x2 = .Left + .Width / 2
        y2 = .Top + .Height / 2
    End With
    
    With ActiveSheet.Shapes.AddLine(x1, y1, x2, y2)
        .Fill.Transparency = 0#
        .Line.Weight = 0.75
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Line.Transparency = 0#
        .Line.Visible = msoTrue
        .Line.ForeColor.SchemeColor = 12
        .Line.BackColor.RGB = RGB(255, 255, 255)
        .Line.BeginArrowheadLength = msoArrowheadLengthMedium
        .Line.BeginArrowheadWidth = msoArrowheadWidthMedium
        .Line.BeginArrowheadStyle = msoArrowheadOval
        .Line.EndArrowheadLength = msoArrowheadLengthMedium
        .Line.EndArrowheadWidth = msoArrowheadWidthMedium
        .Line.EndArrowheadStyle = msoArrowheadOval
    End With
End Sub

[clownfish]

Jeff and Shg:
I will give these a try tomorrow and will mark the post as solved then -- thanks very much to both of you!