Hi all,
I have the macro below, in the BOLD part, it finds TODAY'S DATE and make that cell active.

How do I change it to FIND THIS MONTH?

My columns include months such as 8/1/07 display as "Aug 07"....
Days do not matter, if it finds THIS MONTH (ex:Aug 07), then make that cell active. Thanks

Sub Macro1()
Dim rng1 As Range
    Dim dateToFind As Date
    Dim foundDate As Range

    dateToFind = Date 'This is today's date

    Set rng1 = Range(Cells(4, 2), _
Cells(Columns.Count, 1).End(xlToRight))
    Set foundDate = rng1.Find(What:=dateToFind, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

    If Not foundDate Is Nothing Then
    foundDate.Select
    Else
    MsgBox dateStr & " not found"
    End If
    ActiveCell.Offset(10, 0).Select
    Set leftCell = ActiveCell
    Set RightCell = Cells(ActiveCell.Row, 29)
    Range(leftCell, RightCell).Select
    Selection.Copy
    ActiveCell.Offset(-6, 0).Select
    Selection.PasteSpecial Paste:=xlValues
    ActiveCell.Offset(6, 0).Select
    ActiveCell.ClearContents
End Sub

change the line
dateToFind = Date
to
dateToFind = Month(Date)

 then try to run the macro.
your macro will find only first entry August.
If there are more entires in August this macro will not find.
for that you have to a do loop with "findnnext"
see help under "findnext"

besides your rng1 defines as written by you as
$B$4:$IV$256

is this what you want

after set rng1=......
add a line
msgbox rng1.address
and see what you get.
venkat

supplement to my reply

change
dim datetofind as integer

it does not work.

I have 8/1/07 formatted as "Aug 07" in my range but it does not select that cell.

The first time I ran, it selected "Aug 06", but then I ran the macro again, everytime after that it selected the cells below Aug 06 that has an integer (999).

I only used the "BOLD" part to test the macro (placed it b/w Sub and end Sub), if "This Month" is selected as an active cell, then the rest of the macro is fine.


My range is A4:AC4, but I don't know how to tell it to stop at AC4 so I let it search to the end of row 4. These are month/year in incremental order, so there will be no 2 of the same.

Thanks,