Search and get correspondent positions

excel-2.jpg
excel-help-needed.xlsx

This file has got few cells which are showing "NO", I want to search this grid i.e P1 to AD23, check that where are the "no" cells located and than in cells R31 and S31, want the corresponding values of each "no" cell.

For e.g:

For P4 the value is "no" so in R31 I need 1478 and in S31 I need 1912, that are two values of "no" in P4.

Similarly for next "no" i.e Q3, I need values in next row R32=1478 and S32=2285

it should continue in rows for each "no", in this file, there are 11 cells with "no" so there should be 11 rows showing 2 corresponding values of each "no".

Many Thanks.

Maybe:

Sub KamranMustafa()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Range("R30").Value = " "
Range("S30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value
            Range("S" & Rows.Count).End(3)(2).Value = rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value
            Range("S" & Rows.Count).End(3)(2).Value = rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Originally Posted by JOHN H. DAVIS

Maybe:

Sub KamranMustafa()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Range("R30").Value = " "
Range("S30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value
            Range("S" & Rows.Count).End(3)(2).Value = rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value
            Range("S" & Rows.Count).End(3)(2).Value = rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

You are a star it worked great, perfectly as I wanted. Thank you so so much

Can you make a little change, instead of using two columns, can we show results in 1 column with comma separated values. The result in rows is ok, but instead of using columns R and S, can we use only R and show both the values with comma separation?

Do you mean like this?

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Range("R30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Originally Posted by JOHN H. DAVIS

Do you mean like this?

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Range("R30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Perfectly this....thank you thank you thank you so very much

You're welcome. Glad to help out and thanks for the feedback.

Hi

IS there a way that I can reference it to another sheet, for e.g I have the grid which has got "no" values on Sheet 2 so I need to search "no" on sheet 2 and display results on sheet 1.

Originally Posted by xlbiznes

Just change this line of code of @JOHN H. DAVIS

from

 For Each rcell In Range(Cells(2, i), Cells(23, i))

to

 For Each rcell In sheets("sheet2").Range(Cells(2, i), Cells(23, i))

Run-time error: 1004
Application-defined or object-defined error

Originally Posted by xlbiznes

Just change this line of code of @JOHN H. DAVIS

from

 For Each rcell In Range(Cells(2, i), Cells(23, i))

to

 For Each rcell In sheets("sheet2").Range(Cells(2, i), Cells(23, i))

When i run it on sheet2, it is working fine but i need results in sheet 1, where it is giving following error:

Run-time error: 1004
Application-defined or object-defined error

Originally Posted by xlbiznes

This code should be under the sheet1 code window.

Yes I tried that on sheet1, it is still showing same error

Maybe:

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Range("R30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Originally Posted by JOHN H. DAVIS

Maybe:

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Range("R30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Thank you so much, working perfect!

Originally Posted by JOHN H. DAVIS

Maybe:

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Range("R30").Value = " "
For i = 16 To 30
    For Each rcell In Range(Cells(2, i), Cells(23, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Sheets("Sheet1").Range("R" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Application.ScreenUpdating = True
End Sub

Agadir report complete - (DOC 2).xlsm

In this file I want the result in 'Sheet1' Cell 'A303', I tried putting it but it's showing blank no error, no results :/ can you check please

Try:

Sub KamranMustafazz()
Dim rcell As Range
Dim i As Long
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Rows("1:1").Insert
For i = 1 To 30
    For Each rcell In Range(Cells(2, i), Cells(243, i))
        If rcell.Value = "No" And rcell.Offset(-1).Value <> "" Then
            Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2).Value = rcell.Offset(-1).Value & ", " & rcell.Offset(1).Value
        End If
        If rcell.Value = "No" And rcell.Offset(-1).Value = "" Then
            Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2).Value = rcell.Offset(, -1).Value & ", " & rcell.Offset(, 1).Value
        End If
    Next rcell
Next i
Sheets("Sheet2").Rows("1:1").Delete
Application.ScreenUpdating = True
End Sub

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Sorry for getting late, can I limit the results to 15, like the number of "no", if they are above 15, than in my final sheet 1 list, i just get 15 results and not more?