macro for creating matrix rows for Node optimization

Question

I am currently working on a traveling sales person problem in Excel. For this I need to generate two rows parallel to each other ( Row1 for row Row2 for column) and use INDEX MATCH MATCH to look up the distances between nodes and solve using solver in order to get the best sequence to minimize distance.

For example I have row0 of four nodes named [1][2][3][4].

Distance matrix
__[1][2][3][4]
[1] 0, 4, 5, 7
[2] 4, 0, 6, 2
[3] 5, 6, 0, 1
[4] 7, 2, 1, 0

It must take row 0 and using a macro to develop the following 2 rows

Row 1 [1][1][1][2][2][3]
Row 2 [2][3][4][3][4][4]

Then I can use the Index Match Match to automatically bring all the distances below in a third row:

Row 1 [1][1][1][2][2][3]
Row 2 [2][3][4][3][4][4]
Row 3 4, 5, 7, 6, 2, 1

Please I need help developing the macro to create row 1 an row 2. This should be able to account for any amount of nodes and not just the 4 listed in the example.

I don't think anyone is just going to write the code for you. You will do better to post code you have written and then ask about what is not working. — Byron Wall, May 04 '15 at 15:35
The node names isn't necessarily ascending meaning the names in row0 might be [9][2][3][5][1][11] — Claudio Clouds Wyss, May 04 '15 at 16:11

score 0 · Answer 1 · answered May 04 '15 at 17:38

I got the answer for row 2. Sorry if it seems like a basic question but I am a noob at VBA and hoping to get better. Ill post when I have the answer for the next row.

Sub Macro1()
'
' Macro1 Macro
k = 5
i = 0
j = 0
rwar = Worksheets("Sheet1").Cells(13, 5).Value
For i = 3 To rwar + 1
rwar = rwar - 1
    For j = 1 To rwar
    Worksheets("Sheet1").Cells(16, k) = Worksheets("Sheet1").Cells(i, 5).Value
    k = k + 1
    Next j

Next i


'
End Sub

macro for creating matrix rows for Node optimization

1 Answers1