Search using formula and get multiple results in rows

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.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
From the picture you have included, what results do you expect in R32, R33 and R34 ?
 

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
From the picture you have included, what results do you expect in R32, R33 and R34 ?

I want to get the cell addresses of cells in which word "no" is appearing.
 

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
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:

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
Can you tell me how to apply Macro, I haven't done it before.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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"
 

Kamran

New member
Joined
Jan 2, 2014
Messages
11
Reaction score
0
Points
0
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"

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.
 
Top