Script to loop match initial search term in data column to find the value of cell below it and then search that term and carry on

[Fuzzyfazzy]

Will try to explain as best as I can, I have a set of data in Column A.
I would like to run a script which searches a value (example shows I want to search for "A" shown in cell "C1".
So it searches for the "A" and finds it in cell A4 and outputs the value of the cell below it, in this case "B".
The script then continues to search for the next "B" and returns the value below that, i.e. "F", it then searches for the next "F" giving an output of "T" and so on.
Can anyone help?
I hope I have explained it as best as I can.
Many thanks.

	Initial Search	A
Data		Output
A		B
B		F
C		T
D		P
A
B
F
G
A
F
T
A
E
T
P

<tbody>
</tbody>

<strike></strike>

 

[MickG]

Try this for results in starting "C4"

[COLOR="Navy"]Sub[/COLOR] MG06Jan48
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 3
Ans = Range("C1").Value
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 4 To Lst
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
        [COLOR="Navy"]If[/COLOR] .Value = Ans [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            Ans = .Offset(1)
            Cells(c, "C") = Ans
            n = n + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fuzzyfazzy]

Many Thanks MickG, worked perfectly!

 

[Fuzzyfazzy]

Hi MickG, the code works really well, better than I had expected so thankyou very much.

If I could trouble you once again, is there a way to use the Data set as the value to be searched?

So rather than me stating an initial value, could the script run through the each line in the data set and output the results in separate columns?

Sorry to be a pain but the original code worked so well.

Thankyou in advance for your help.

 

[MickG]

Try change the initial "Ans" address, at top of code, to "A4" as below:-

Ans = Range("A4").Value

The results should be the same, if you are expecting a different result, please show an example of data and expected result.

 

[Fuzzyfazzy]

Hi Mick, Thankyou for your quick reply, I have pasted a sample below:

So the script would cycle through the input data (and start from that point) and output the values in a new column.

Thanks again for your help!

Input Data	Output for value of A2	Output for value of A3	Output for value of A4	Output for value of A5	Output for value of A6
A	B	C	D	A	B
B	F			F
C	T
D	P
A
B
F
G
A
F
T
A
E
T
P

<tbody>
</tbody>

 

[Fuzzyfazzy]

Hi Mick, yes, that would give me the output for the value in A4, but how would I cycle through all the values in Column A and output the results for each value into a new column? the script you provided would start from the same line used in the input if that makes sense!

 

[MickG]

Try this:-
I'm not sure if this is what you want, If not please show example of data and expected Result.

[COLOR="Navy"]Sub[/COLOR] MG06Jan15
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4", Range("A" & Rows.Count).End(xlUp))
c = 3
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Ans = Dn.Value
    [COLOR="Navy"]For[/COLOR] n = Dn.Row To Lst
        [COLOR="Navy"]With[/COLOR] Range("A" & n)
            [COLOR="Navy"]If[/COLOR] .Value = Ans [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ans = .Offset(1)
                Cells(c, "C") = Ans
                n = n + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fuzzyfazzy]

Hi Mick, its working great but its adding all the answers into one column, is there a way to split into separate columns?

sorry about this.

 

[MickG]

Try this for Results starting "B4"

[COLOR="Navy"]Sub[/COLOR] MG07Jan29
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4", Range("A" & Rows.Count).End(xlUp))
ac = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 3: Ans = Dn.Value: ac = ac + 1
    [COLOR="Navy"]For[/COLOR] n = Dn.Row To Lst
        [COLOR="Navy"]With[/COLOR] Range("A" & n)
            [COLOR="Navy"]If[/COLOR] .Value = Ans [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Ans = .Offset(1)
                Cells(c, ac) = Ans
                n = n + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick