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

nsb

New Member
Joined
Mar 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Attachments

  • Date return.JPG
    Date return.JPG
    34.8 KB · Views: 5

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,018
Office Version
  1. 2016
Platform
  1. Windows
How about this?

VBA Code:
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
 
  • Like
Reactions: nsb
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,966
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

nsb

New Member
Joined
Mar 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,018
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,966
Office Version
  1. 2016
Platform
  1. Windows
  • Like
Reactions: nsb

nsb

New Member
Joined
Mar 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Formula option:

Book3.xlsx
ABCDEFG
1FromTo8/3/20219/3/202110/3/202111/3/202112/3/2021
28/3/202112/3/2021FIELDFIELDFIELDFIELDFIELD
310/3/202110/3/2021HOMEHOMEFIELDHOMEHOME
49/3/202110/3/2021HOMEFIELDFIELDHOMEHOME
58/3/202112/3/2021FIELDHOMEHOMEHOMEFIELD
Sheet823
Cell Formulas
RangeFormula
A2:A5A2=INDEX(C$1:G$1,MATCH("FIELD",C2:G2,0))
B2:B5B2=LOOKUP(2,1/(C2:G2="FIELD"),C$1:G$1)

Formula option:

Book3.xlsx
ABCDEFG
1FromTo8/3/20219/3/202110/3/202111/3/202112/3/2021
28/3/202112/3/2021FIELDFIELDFIELDFIELDFIELD
310/3/202110/3/2021HOMEHOMEFIELDHOMEHOME
49/3/202110/3/2021HOMEFIELDFIELDHOMEHOME
58/3/202112/3/2021FIELDHOMEHOMEHOMEFIELD
Sheet823
Cell Formulas
RangeFormula
A2:A5A2=INDEX(C$1:G$1,MATCH("FIELD",C2:G2,0))
B2:B5B2=LOOKUP(2,1/(C2:G2="FIELD"),C$1:G$1)

Thank you very much!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,966
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,736
Messages
5,626,592
Members
416,194
Latest member
Dhisilva_aguiar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top