VBA if value found in cell, return column header name 1

[SBuzzT]

Hi excel gods

I am searching for an value in column C of a worksheet. If the exact value is found (it will be a unique value on each row), I need to check the whole row and return the column header name for each cell which has the value "Y". For example, if I searched for and located 123 in C5, and and D5 and G5 contained Y, the result would be D5, G5.

Note: I'm using column letters in the example, but the column headers will actually be names which I need to return comma separated.

I have the search part working fine and I am returning the values for each of the cells in the row, but I'm stuck on how to get each of the column header names.

Can someone point me in the right direction?

 

[SkipVought]

...ON???????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Plz post your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

I'm still playing with this. Now, I get a Compile error: Argument not optional on
For Each c In Intersect(rng.EntireRow, , UsedRange)

Sub ExCheck()

Dim rng As Range, Cel, ms As Worksheet, ws As Worksheet, k, c As Range
Set ms = Sheets("Search")
Application.ScreenUpdating = 0

     With ms
       Cel = .Range("C3")
     End With

     For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> ms.Name Then
            If ws.Name = "SNAME" Then

                With ws.Range("C:C")
    
                        If Len(Cel) Then
                              
                                Set rng = .Find(Cel, LookIn:=xlValues, LookAt:=xlWhole)
                                                                                                                                                                  
                                   If Not rng Is Nothing Then
                                        rng.Offset(, 1).Copy
                                        ms.Range("C6").PasteSpecial xlValues
                                        rng.Copy
                                        ms.Range("D6").PasteSpecial xlValues
                                        'Range("C3").Select
                                        'Range("C3").ClearContents

                                            For Each c In Intersect(rng.EntireRow, , UsedRange)
                                                If c.Value = "y" Then
                                                    k = k & Intersect(c.EntireColumn, .UsedRange.Cells(1, 1).EntireRow) & ","
                                                End If
                                            Next
                                        'ms.Range("E6") = Mid(k, 2)

                                   End If

                        End If
         
                End With
                
            End If
            
        End If
 Next

   
Application.CutCopyMode = 0
Set ms = Nothing
Set rng = Nothing
Application.ScreenUpdating = True


End Sub

 

[SkipVought]

You missapplied the UsedRange...

For Each c In Intersect(rng.EntireRow, , [highlight]ws.[/highlight]UsedRange)

UsedRange is a Worksheet property.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

I actually thought of that one. Still same error (just tried again to make sure I wasn't crazy).

 

[SkipVought]

sorry, only ONE comma

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

lmao! OK, this is working - Finally. Thanks a lot skip. [buying virtual beer]

Just a note: I had actually tried that too and gotten an error as well. Just now I realized I had not also updated the other instance of UsedRange. oye

k = k & Intersect(c.EntireColumn, [highlight #FCE94F]ws[/highlight].UsedRange.Cells(1, 1).EntireRow) & ",

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]