Script to loop match initial search term in data column to find the value of cell bel

[fuzzyfazzy]

Hi All,

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.


21302

[offthelip]

this code will do what you want I think:



Sub test()

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
Range(Cells(4, 3), Cells(lastrow, 3)) = ""


outarr = Range(Cells(4, 3), Cells(lastrow, 3))


srchar = Cells(1, 3)
ind = 1
outarr(ind, 1) = srchar
ind = ind + 1


For i = 4 To lastrow - 1
If srchar = inarr(i, 1) Then
i = i + 1
srchar = inarr(i, 1)
outarr(ind, 1) = srchar
ind = ind + 1

End If
Next i


Range(Cells(4, 3), Cells(lastrow, 3)) = outarr


End Sub

[fuzzyfazzy]

That's perfect! worked brilliantly! thankyou!