How to find the first cell containing a "Y"

[ds_robb]

I have a string of cells that go from Jan-15 to Sep-17 Horizontally (H3 to AZ3) and then below that I have cells across multiple lines (also H to AZ) containing either a Y or N indicating if any data was sent in that month. Does anyone know how I can check on the range to find when the first Y occurs and then report back the corresponding month to cell A


EG:

On line 4, cells H, J, K and L = N and M = Y Thus making the first occurrence of Y at M4 corresponding to cell M3 which is May-15 Therefore data was first transmitted on May 15. Cells N, O and onwards are likely to have Y's ongoing but could also have an N is no data was transmitted that month. I simply need the first Y


Many thanks

 

[AhoyNC]

Try:
You may need to format cell A1 as date.

Excel Workbook
	A	B	H	I	J	K	L	M	N	O
1	5/15/2018
2
3			1/1/2018	2/5/2018	3/16/2018	4/5/2018	4/30/2018	5/15/2018	5/25/2018	6/1/2018
4			N	N	N	N	N	Y	N	Y
Sheet

 

[alansidman]

Here is a VBA solution

Option Explicit


Sub FindY()
    Dim c As Range
    Dim rng As Range
    Dim x As Date
    Set rng = Range("H4:AZ4")
    For Each c In rng
        If c = "Y" Then
            x = c.Offset(-1, 0)
        End If
    Next c
    MsgBox ("Your First Y is " & x)
    Exit Sub


End Sub

 

[MARK858]

Hi Alan I think you need to move the lines below into the If statement as if there is more than one "Y" the code as is will give the message box for the last "Y".

    MsgBox ("Your First Y is " & x)
    Exit Sub

 

[Rick Rothstein]

I have a string of cells that go from Jan-15 to Sep-17 Horizontally (H3 to AZ3) and then below that I have cells across multiple lines (also H to AZ) containing either a Y or N indicating if any data was sent in that month. Does anyone know how I can check on the range to find when the first Y occurs and then report back the corresponding month to cell A

EG:

On line 4, cells H, J, K and L = N and M = Y Thus making the first occurrence of Y at M4 corresponding to cell M3 which is May-15 Therefore data was first transmitted on May 15. Cells N, O and onwards are likely to have Y's ongoing but could also have an N is no data was transmitted that month. I simply need the first Y

You have more than one row of N's and Y's, correct? And you want the date for the first Y in Column A for the row that Y is on, correct? If so, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub FirstWyes()
  Dim R As Long, Dates As Variant, Data As Variant, Result As Variant
  Dates = Range("H3:AZ3")
  Data = Intersect(Columns("H:AZ"), Range("4:" & Cells(Rows.Count, "H").End(xlUp).Row))
  ReDim Result(1 To UBound(Data), 1 To 1)
  On Error Resume Next
  For R = 1 To UBound(Data)
    Result(R, 1) = Dates(1, InStr(Join(Application.Index(Data, R, Evaluate("TRANSPOSE(ROW(1:45))")), ""), "Y"))
  Next
  On Error GoTo 0
  Range("A4").Resize(UBound(Result)) = Result
  Columns("A").AutoFit
End Sub[/td]
[/tr]
[/table]