Search using formula and get multiple results in rows

K

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
excel.jpg

I want to search the word "no" from P1 to AD23 and display the results in rows R32,R33,R34 and so on depending upon results. Please help.
 
From the picture you have included, what results do you expect in R32, R33 and R34 ?
 
NoS said:
From the picture you have included, what results do you expect in R32, R33 and R34 ?
Click to expand...

I want to get the cell addresses of cells in which word "no" is appearing.
 
I want the Cell Address of the cell where word "no" is appearing in these cells:

R32
R33
R34
R45
and so on

For e.g: P5 has "no" so in R32 it should show P5, after that Q4 should appear in R33, than Q8 should appear in R34 ... and so on....
 
Last edited:
Kamran, you have posted this question in the Formulas section and I don't know how to do this with a formula, but I'm pretty sure this macro will do it.

Code: 
Sub GetPositions()

    Dim rng As Range
    Dim cel As Range

'range to search
Set rng = Range("P1:AD23")

For Each cel In rng
    'take variations of no into account
    If UCase(Trim(cel.Value)) = "NO" Then
    'if no is found, put address in next available row in col R    
    Range("R" & Cells(Rows.Count, "R").End(xlUp).Row + 1).Value = cel.Address
    End If
Next cel

End Sub
 
Can you tell me how to apply Macro, I haven't done it before.
 
Clicking on the link opens it for me.
If not for you then do a Google search for "contextures adding code to an Excel Workbook"
 
NoS said:
Clicking on the link opens it for me.
If not for you then do a Google search for "contextures adding code to an Excel Workbook"
Click to expand...

Thanks you so much. I got it working. I am posting another thread in Macros section, there is lot more I need to with. Thank you once again. See if you can help me in my new thread.
 
You must log in or register to reply here.
Back
Top