1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: line between coordinates using excel?

Discussion in 'Business Applications' started by arrrgh2003, Dec 12, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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 :)
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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
     
  3. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  5. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Either a For/Next or Do/While loop.
    If you can post the spreadsheet (or part of it) we could help you.
     
  7. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Are there any blank rows in the data?
     
  9. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    arrrgh, I will have some code for you soon, (i hope).
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  12. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    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!
     
  13. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    SOLVED!

    Thanks for your time and effort OBP, It's now working and will I adapt it to work in the full sheet.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    arrrgh, can you mark the thread as solved using the "Thread Tools" at the top right of this page please?
     
  15. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/424527