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

F

Fuzzyfazzy

New Member
Joined
Jan 4, 2018
Messages
9
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.


[TABLE="width: 215"]
<tbody>[TR]
[TD][/TD]
[TD]Initial Search[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD][/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Try this for results in starting "C4"
Code: 
[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
 
Upvote 0
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.
 
Upvote 0
Try change the initial "Ans" address, at top of code, to "A4" as below:-
Code: 
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.
 
Last edited:
Upvote 0
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!

[TABLE="width: 805"]
<tbody>[TR]
[TD]Input Data[/TD]
[TD]Output for value of A2[/TD]
[TD]Output for value of A3[/TD]
[TD]Output for value of A4[/TD]
[TD]Output for value of A5[/TD]
[TD]Output for value of A6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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!
 
Upvote 0
Try this:-
I'm not sure if this is what you want, If not please show example of data and expected Result.
Code: 
[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
 
Upvote 0
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.
 
Upvote 0
Try this for Results starting "B4"
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
VBA for extracting specific data from PDF Docs
Replies
3
Views
760
xcellrodeo
X
J
  • Question Question
VBA Search box and button code
Replies
0
Views
272
jlcodes
J
T
  • Solved
Making the contents of the active cell the search term, and search using that term, copy paste row, next
Replies
2
Views
404
Travis Kunnen
T
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie
F
  • Question Question
Search for Specific Value - For Every Match, Combine into One Cell.
Replies
7
Views
338
FrEaK_aCcIdEnT
F

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top