Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: line between coordinates using excel?


(!)

arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
12-Dec-2005, 03:12 PM #1
Solved: line between coordinates using excel?
Hi,
I'm using Win XP SP2, Office 2003-SP2

Is it possible to draw lines between sets of cordinates with VBA if I have a table as such:

siteA X1 Y1 SiteB X2 Y2 Line width Colour
ABC 21.43 39.74 DEF 21.12 39.65 1 Black
DEF 21.12 39.65 XYZ 20.98 39.12 2 Red

I can't figure it out and can't find any similar posts
If I get a general point in the right direction, I should be able to work it out
Thanks
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
12-Dec-2005, 04:38 PM #2
I have just produced this VB by recording a macro of drawing a line.
Range("O37").Select
ActiveSheet.Shapes.AddLine(65.25, 326.25, 132#, 407.25).Select
perhaps that helps
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
13-Dec-2005, 03:22 AM #3
Hi OBP,
Thanks for the input.
I want it to draw the line automatically for every row in the sheet based on the 2 sites ie. ABC and DEF

The general idea is a transmission network between GSM sites, they all connect in a 'tree topology' so each site has a connection direct or via another site to the hub site.
It could be done manually putting in each point but it would take weeks to do

any further ideas, thanks
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
13-Dec-2005, 07:19 AM #4
You need to put your co-ordinates in to the line drawing I gave you, if that works for a single line you just need to put that VB in to a loop to update them all.
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
13-Dec-2005, 10:53 AM #5
this is where I'm having real trouble. the # in the line was throwing me off, i couldn't see where the coordinates were to go.

I think I have that part now, but what kind of loop should I use? do you have any idea for this. It's a long time since i did things in code. IF,THEN, ELSE is what I was thinking but that could be 20 years out of date lol
Any sample code I could develope?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
13-Dec-2005, 01:52 PM #6
Either a For/Next or Do/While loop.
If you can post the spreadsheet (or part of it) we could help you.
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
13-Dec-2005, 03:32 PM #7
Hi again, thanks for your time

Attached is a few rows and the columns from the sheet that are applicable.
I'll look at how to do the loops you suggested in the mean while
Attached Files
File Type: xls tsg_sample.xls (20.0 KB, 169 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
14-Dec-2005, 04:58 AM #8
Are there any blank rows in the data?
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
14-Dec-2005, 06:10 AM #9
the only blank cells will be after the data, so it can loop until it see's a blank cell. (if that's what the question was for.)

Thanks for your continued help
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
14-Dec-2005, 06:23 AM #10
arrrgh, I will have some code for you soon, (i hope).
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
14-Dec-2005, 06:34 AM #11
Here is the code.

Private Sub CommandButton1_Click()
Dim c1 As Double, c2 As Double, c3 As Double, c4 As Double, count As Integer, counter As Integer
Range("a1", Range("a65536").End(xlUp)).Select
count = Selection.Rows.count - 1

For counter = 1 To count
Range("a1").Select

If IsEmpty(ActiveCell.Offset(counter, 1)) = False Then
c1 = ActiveCell.Offset(counter, 1)
c2 = ActiveCell.Offset(counter, 2)
c3 = ActiveCell.Offset(counter, 3)
c4 = ActiveCell.Offset(counter, 4)
MsgBox counter & " " & c1

ActiveSheet.Shapes.AddLine(c1, c2, c3, c4).Select
End If
Next counter

End Sub

You have the problem that your co-ordinates do not equate to positions on an Excel spread sheet, or it could be that I have not used them in the correct order for drawing the line.
Perhaps you can work it out from here.
The message box does not need to be there, it is just to let you know where the loop is for testing.
__________________
OBP
I do not give up easily
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
14-Dec-2005, 12:49 PM #12
Thanks very much OBP,

I'll have a play with that tonight and get back to you. That code is nothing like what I have been scribbling down and trying so I'll try a few VBA online courses!
arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
15-Dec-2005, 04:42 AM #13
Solved!
SOLVED!

Thanks for your time and effort OBP, It's now working and will I adapt it to work in the full sheet.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
15-Dec-2005, 05:30 AM #14
arrrgh, can you mark the thread as solved using the "Thread Tools" at the top right of this page please?
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑