search numbers in 2 cols

**VBisgreat** · 05-12-2009, 04:39 PM

Hi All:

I'm just starting to use VB and I have a little problem in which I need as much help as possible:

I have a path of number

1 2 3 4 5
..........6 7
This path of number can be write in two columns of numbers:
1 2
4 5
2 3
3 6
7 6
3 4
I want to write a program that will start out to search for number 1 in col 1, if it is found in col1 then it next number is in col2, if it is found in col2 then its next number is in col1. I want the program to map it out so it will look like this:
1 2 3 4 5
1 2 3 6 7

Notice the number start to split at 3 to 3 4 and 3 6. My program right now just pick the first path and finish it without coming back to the second path, I need help with a code so that it can come back for the second path.

I will be very thankful if someone can help me with this.

Sincerely,

VBisgreat

sorry, I couldn't draw the path into a ---< shape
1 2 3 4 5
..........6 7

**StephenR** · 05-12-2009, 05:18 PM

That's quite hard to follow. Your description brings to mind vaguely probability trees, but I could be barking up the wrong tree.

Can you attach a sample workbook which illustrates how your data are laid out and desired results?

**VBisgreat** · 05-12-2009, 06:47 PM

>>>See attachment for what I have so far the scan pic shows the relationship of all the numbers( open the file, tools,macro,Visual Basic Editor, open Module4, Click on Sub FindAnotherWay screen, Click Play button, Go Back to Excel file, Click on TestResults sheet to see output. Three pieces still missing from the split because the program just proceed and complete with the first one found. The format of this one is a little bit different than the problem posted above but same idea.

The Program will do something like this when you click play:
1)Find the first number in col C Bank_LS_Bus.
2)Then you start to search for it in both colA and colB in Branches sheet. First, you start to search for it in colA, if it is found in colA then it next number is in colB, if it is found in colB then its next number is in colA. For example if you want to get from 10983 to 10964; you look into colA and didn't find 10983 then you look into colB and found it so its next number (10964) is in col B.

3)Compare and ignore repeated segment that already recorded so we don't go backward.
4)Find just one time;Segment already recorded implied going backward so it is End of line (EOL).Stop
5)Find Two Times then it is non-split; one go backward and one go forward. Keep Searching.
6)Find Three Times: one goes backward, the other two go forward, this implied a split (This is the toughest part, my program right now can deal with everything else but split)
7)ColE in Branches sheet = 0 Normally Open (N.O.) Stop. Move to next number.
8) when Complete with the first number in col C in the Bank_LS_Bus sheet, move to the next number on the list and repeat the process

I hope to improve the code so that the program can go back to the split and pick up the other path too. The missing pieces are in blue in the TestResults sheet. Right now it just goes with whatever number it can find first.

Thank you

VBisgreat