Find first occurence of a string

[DHogg]

I have some data with an output similar to this:
01APR2013:10:43:41.134000

There are thousands of lines of data from various months. I want a macro which will select the cell in a column which is the first day of the month. So basically, work out what the first day of the current month is, and find the first result in the column so I can then select all data below that and copy/paste to another location. I thought using something like search, today, day, and month function would work, but some of these are not in VBA.

Any ideas?

 

[Robert Mika]

Uisng formula:

Excel 2010
	A	B	C
1	04APR2013:10:43:41.134000		01APR2013:10:43:41.134012
2	04APR2013:10:43:41.134001
3	04APR2013:10:43:41.134002
4	04APR2013:10:43:41.134003
5	04APR2013:10:43:41.134004
6	04APR2013:10:43:41.134005
7	02APR2013:10:43:41.134006
8	04APR2013:10:43:41.134007
9	04APR2013:10:43:41.134008
10	04APR2013:10:43:41.134009
11	04APR2013:10:43:41.134010
12	04APR2013:10:43:41.134011
13	01APR2013:10:43:41.134012
14	01APR2013:10:43:41.134013
15	01APR2013:10:43:41.134014
16	01APR2013:10:43:41.134015
17	10APR2013:10:43:41.134016
18	01APR2013:10:43:41.134017
Sheet1

Cell Formulas
Range	Formula
C1	{=INDEX($A$1:$A$18,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),--LEFT($A$1:$A$18,9),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

I have some data with an output similar to this:
01APR2013:10:43:41.134000

There are thousands of lines of data from various months. I want a macro which will select the cell in a column which is the first day of the month. So basically, work out what the first day of the current month is, and find the first result in the column so I can then select all data below that and copy/paste to another location. I thought using something like search, today, day, and month function would work, but some of these are not in VBA.

Any ideas?

Give this macro a try...

Sub FindFirstOfCurrentMonth()
  On Error Resume Next
  Columns("A").Find("01" & Format(Date, "mmm") & Year(Now) & "*", Cells(Rows.Count, _
                    "A"), LookAt:=xlPart, SearchDirection:=xlNext).Select
  If Err.Number Then MsgBox "Cannot find first day of this month!"
  On Error GoTo 0
End Sub

 

[DHogg]

Wow, two great solutions. Robert Mika, would you mind explaining(or try to) the formula? The match part kind of lost me. I'm normally good with long array formulas but not that one.

 

[vds1]

Just giving a try,

In C2

=VLOOKUP(TEXT(EOMONTH(LEFT(A2,9),-1)+1,"ddmmmyyyy")&"*",A:A,1,0)