+ Reply to Thread
Results 1 to 7 of 7

code for cell referencing in VBA

  1. #1
    Registered User
    Join Date
    04-20-2007
    Posts
    5

    Exclamation code for cell referencing in VBA

    I am currently trying to solve the travelling salemsan problem using VBA. I have created a distance matrix for 20 cities. I also have a two column table with cities 1-20 listed in the first column and the corresponding distances in the second column. For example, from the table below the salesman starts at city 1 then goes to 2, then 3 etc. The distance between city 1 and 2 being 8km etc.
    City Distance
    1 0
    2 8
    3 4
    4 3
    I have a code that swaps the cities around, but I don't know how to write a code that also changes the distances with it. If I swap city's 2 and 3 around, the distance between city 1 and 3 is obviously not 8km. Can someone please help?
    The code I to swap two cells is,

    Please Login or Register  to view this content.
    I'm not sure how to use this code to change the distances as well.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try with this code:

    Please Login or Register  to view this content.
    I hope it's what you need.

    Regards,
    Antonio

  3. #3
    Registered User
    Join Date
    04-20-2007
    Posts
    5
    AssignmentData.zipThanks for the code. I used the code and it half did what I wanted it to do. I have attached an excel file and hope it will explain my problem a bit more. If you click on sheet 2, you can see the 20x20 matrix above and a list of the twenty cities below with their corresponding distances. When I highlight, say cells B26 and B27, and run the code the distances change as well but the distances are wrong; the distance between city 3 and 2 is not 12.474 but is 7.9209 (from the matrix). How can I change the code?

  4. #4
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346
    Quote Originally Posted by KK1811
    Attachment 10223Thanks for the code. I used the code and it half did what I wanted it to do. I have attached an excel file and hope it will explain my problem a bit more. If you click on sheet 2, you can see the 20x20 matrix above and a list of the twenty cities below with their corresponding distances. When I highlight, say cells B26 and B27, and run the code the distances change as well but the distances are wrong; the distance between city 3 and 2 is not 12.474 but is 7.9209 (from the matrix). How can I change the code?
    I have not quite understood your application. Obviously when you swap the cities ( B26<>B27) you cannot simply swap C26<>C27. You will need to refer to your distance matrix and find out the new distance ( say from city1 to city3 ) and write it to C26. But I want to know - why are you doing this? May be there is a simpler way to solve your actual problem. Is it that you want to enter city names in column B in the order in which your sales rep visited them and you want excel to fill up the col B which has distance? ie On sheet2 I can see that you have filled up the values C25 to C44 manually by referring to the distance matrix but you want the a macro to do it?

    A V Veerkar

  5. #5
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    See the attached file... I hope it's what you need.

    Regards,
    Antonio
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    I still have not figured out what the question is. Surelly you do not wnat to compare all variations for moving between the 20 cities. Do you just want to put the 20 cities in some order, and then have the distances update? That can be done from your table with a formula of the type

    =vlookup(city 1, citytable, match(city 2, citytable, 0),false)

    where city 1 is the "from" city and city 2 is the "to" city and citytable is your table
    not a professional, just trying to assist.....

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello KK1811,

    The attached workbook is yours with the swap macro installed. This macro allows you to change rows that are sequential like B27 and B28, or non-sequential like B27 and B33. If case you don't know how to select non-sequential rows, you make your first selection and then press and hold the CTRL key to add another selection. Only the first row and last row of a selection are used regardless if it sequential (also called contiguous) or non-sequential (also called disparate). Here is the macro code...

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1