How to get a cell location
 

My question is about how to determine a cell location, I mean, if I use:
ActiveSheet.Shapes.AddLine(2, 2, 10, 10)
Then the location starts at 2,2 and ends at 10,10, but if I want that the
line starts at the center of cell A5 and ends at the center of C10 ¿How to
accomplish it???
I need the preceding because I have a sheet with numbers:

A B C D
1 1
2 2 0 1
3 3 1 2
4 4 2 3
5 5 3 0 4
6 6 4 1 5
7 7 5 2 6

And this numbers represent grades of an especial test, each test (columns)
has its own numeric scale (rows) but a graphic should be made to present the
results of the test.
The user grades could be 1,4,1,4 then lines must be drown from 1 to 4 then
from 4 to 1 then from 1 to 4
Hope this have sense
Thanks

How to get a cell location
 

Give this a try...

Sub LineStuff()
Dim shp As Shape
Dim rngStartCell As Range
Dim rngEndCell As Range
Dim lngHStart As Long
Dim lngWStart As Long
Dim lngHEnd As Long
Dim lngWEnd As Long

Set rngStartCell = Range("B5")
Set rngEndCell = Range("H10")

With rngStartCell
lngHStart = .Top + .Height / 2
lngWStart = .Left + .Width / 2
End With
With rngEndCell
lngHEnd = .Top + .Height / 2
lngWEnd = .Left + .Width / 2
End With

Set shp = ActiveSheet.Shapes.AddLine(lngWStart, lngHStart, lngWEnd, lngHEnd)

End Sub

--
HTH...

Jim Thomlinson


"filo666" wrote:

My question is about how to determine a cell location, I mean, if I use:
ActiveSheet.Shapes.AddLine(2, 2, 10, 10)
Then the location starts at 2,2 and ends at 10,10, but if I want that the
line starts at the center of cell A5 and ends at the center of C10 ¿How to
accomplish it???
I need the preceding because I have a sheet with numbers:

A B C D
1 1
2 2 0 1
3 3 1 2
4 4 2 3
5 5 3 0 4
6 6 4 1 5
7 7 5 2 6

And this numbers represent grades of an especial test, each test (columns)
has its own numeric scale (rows) but a graphic should be made to present the
results of the test.
The user grades could be 1,4,1,4 then lines must be drown from 1 to 4 then
from 4 to 1 then from 1 to 4
Hope this have sense
Thanks

How to get a cell location
 

thanks Jim

"Jim Thomlinson" wrote:

Give this a try...

Sub LineStuff()
Dim shp As Shape
Dim rngStartCell As Range
Dim rngEndCell As Range
Dim lngHStart As Long
Dim lngWStart As Long
Dim lngHEnd As Long
Dim lngWEnd As Long

Set rngStartCell = Range("B5")
Set rngEndCell = Range("H10")

With rngStartCell
lngHStart = .Top + .Height / 2
lngWStart = .Left + .Width / 2
End With
With rngEndCell
lngHEnd = .Top + .Height / 2
lngWEnd = .Left + .Width / 2
End With

Set shp = ActiveSheet.Shapes.AddLine(lngWStart, lngHStart, lngWEnd, lngHEnd)

End Sub

--
HTH...

Jim Thomlinson


"filo666" wrote:

My question is about how to determine a cell location, I mean, if I use:
ActiveSheet.Shapes.AddLine(2, 2, 10, 10)
Then the location starts at 2,2 and ends at 10,10, but if I want that the
line starts at the center of cell A5 and ends at the center of C10 ¿How to
accomplish it???
I need the preceding because I have a sheet with numbers:

A B C D
1 1
2 2 0 1
3 3 1 2
4 4 2 3
5 5 3 0 4
6 6 4 1 5
7 7 5 2 6

And this numbers represent grades of an especial test, each test (columns)
has its own numeric scale (rows) but a graphic should be made to present the
results of the test.
The user grades could be 1,4,1,4 then lines must be drown from 1 to 4 then
from 4 to 1 then from 1 to 4
Hope this have sense
Thanks