Code to search in Row for String and Return date from the headers.

[nsb]

Hi Everyone.

First of all , you guys rock.

Secondly, I am new to VBA and clueless as to where to begin to solve the following issue.

I Have one sheet with data in 300 rows and 7 columns wide. Please look in the attachment for an example.

What I need the code to do is to search for example the row 2 from column C to G and if it finds the text “Field” then return the date that is in the header of the column the text belongs, to the first cell of the same row.

If in the same row it finds a second text “Field” it should return the date to the second cell in the same row.

I am sorry for my bad English.

Any help on this is much appreciated.

 

[Zot]

How about this?

Option Compare Text

Sub FindDate()

Dim m As Long, n As Long
Dim eRow As Long, ColCount As Long

Application.ScreenUpdating = False

eRow = Cells(Rows.Count, "C").End(xlUp).Row
ColCount = Cells(1, Columns.Count).End(xlToLeft).Column

Set rngRow = Range("A2", Cells(1, ColCount))

For m = 2 To eRow
    For n = 3 To ColCount
        Select Case Cells(m, n)
            Case "FIELD"
                If Len(Range("A" & m)) = 0 Then
                    Range("A" & m) = Cells(1, n).Value
                    Range("B" & m) = Cells(1, n).Value
                Else
                    Range("B" & m) = Cells(1, n).Value
                End If
        End Select
    Next
Next

End Sub

 

[jtakw]

Hi,

What you want can be achieved with pretty simple formulas, if you're interested.

 

[nsb]

Zot,

This is what I want!
Thank you very much !!

You saved me a tone of work.


jtakw,

That would be nice to have too.
Thank you.

 

[Zot]

Zot,

This is what I want!
Thank you very much !!

You saved me a tone of work.


jtakw,

That would be nice to have too.
Thank you.

Thanks for update

 

[jtakw]

jtakw,

That would be nice to have too.
Thank you.

Formula option:

Book3.xlsx
	A	B	C	D	E	F	G
1	From	To	8/3/2021	9/3/2021	10/3/2021	11/3/2021	12/3/2021
2	8/3/2021	12/3/2021	FIELD	FIELD	FIELD	FIELD	FIELD
3	10/3/2021	10/3/2021	HOME	HOME	FIELD	HOME	HOME
4	9/3/2021	10/3/2021	HOME	FIELD	FIELD	HOME	HOME
5	8/3/2021	12/3/2021	FIELD	HOME	HOME	HOME	FIELD
Sheet823

Cell Formulas
Range	Formula
A2:A5	A2	=INDEX(C$1:G$1,MATCH("FIELD",C2:G2,0))
B2:B5	B2	=LOOKUP(2,1/(C2:G2="FIELD"),C$1:G$1)

 

[nsb]

Formula option:

Book3.xlsx
	A	B	C	D	E	F	G
1	From	To	8/3/2021	9/3/2021	10/3/2021	11/3/2021	12/3/2021
2	8/3/2021	12/3/2021	FIELD	FIELD	FIELD	FIELD	FIELD
3	10/3/2021	10/3/2021	HOME	HOME	FIELD	HOME	HOME
4	9/3/2021	10/3/2021	HOME	FIELD	FIELD	HOME	HOME
5	8/3/2021	12/3/2021	FIELD	HOME	HOME	HOME	FIELD
Sheet823

Cell Formulas
Range	Formula
A2:A5	A2	=INDEX(C$1:G$1,MATCH("FIELD",C2:G2,0))
B2:B5	B2	=LOOKUP(2,1/(C2:G2="FIELD"),C$1:G$1)

Formula option:

Book3.xlsx
	A	B	C	D	E	F	G
1	From	To	8/3/2021	9/3/2021	10/3/2021	11/3/2021	12/3/2021
2	8/3/2021	12/3/2021	FIELD	FIELD	FIELD	FIELD	FIELD
3	10/3/2021	10/3/2021	HOME	HOME	FIELD	HOME	HOME
4	9/3/2021	10/3/2021	HOME	FIELD	FIELD	HOME	HOME
5	8/3/2021	12/3/2021	FIELD	HOME	HOME	HOME	FIELD
Sheet823

Cell Formulas
Range	Formula
A2:A5	A2	=INDEX(C$1:G$1,MATCH("FIELD",C2:G2,0))
B2:B5	B2	=LOOKUP(2,1/(C2:G2="FIELD"),C$1:G$1)

Thank you very much!

 

[jtakw]

You're welcome, thanks for the feedback.