Find first date of the month in range and return column number

[jben86]

Hey all,

Having a bit of difficulty getting some code to work here. I have a row of cells R3:BC3 that will contain 38 dates. R3 will be the first of a month, then go out through the month, and the first week-ish of the next month.

For example, this month is september, so R3 will be 8/1/2013 and BC3 will be 9/7/2013.
Next month, October, R3 will be 9/1/2013, and BC3 will be 10/8/2013.

38 days total.

I need to search my range R3:BC3 to find the first the first day of the month, then return the column number. Here's what I have so far, but I'm getting a "Unable to get the Match property of the WorksheetFunction class" error.

Please Login or Register  to view this content.

Thanks in advance for any help!

[JasperD]

use find instead of match...
http://msdn.microsoft.com/en-us/libr.../ff839746.aspx

Please Login or Register  to view this content.

[rcm]

I think there is a data type problem, what is the format in the row 3?

[jben86]

Please Login or Register  to view this content.

I'm getting a syntax error with seperators with that....
Edit: Oh duh, missing paren in first range, no idea how i didn't see that.... code works great thanks! I generally avoid .find as I seem to just never be able to get it to work right. Thanks again.


The format for row 3 is set as a date with 'dd-mmm' format.

Still curious about what wasn't work as intended in my original code though.

[Sa DQ]

Option Explicit
Sub gpeFindFDate()
 Dim Rng As Range, sRng As Range
 Dim Dat As Date:                           Dim Format_ As String
 
 Set Rng = Range([R3], [R3].End(xlToRight))
 Dat = DateSerial(Year(Date), Month(Date), 1)
 Format_ = Rng.NumberFormat
 Rng.NumberFormat = "MM/DD/yyyy"
 Set sRng = Rng.Find(Format(Dat, "mm/dd/yyyy"), , xlValues, xlWhole)
 If Not sRng Is Nothing Then MsgBox sRng.Column
 Rng.NumberFormat = Format_
End Sub 

[jben86]

Please Login or Register  to view this content.

Bah! Ok, just figured it out. You were right rcm, data type problem. I fixed it with Clng.

Please Login or Register  to view this content.