VBA macro to FIND THIS MONTH in a given range

[mvatoi]

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

 

[facethegod]

Try this after your What argument in your Find property

What:=Application.Text(dateToFind,"mmm yy")

HTH

 

[mvatoi]

It does not work

I placed it like this, and it returns 'not found'. I have the date in there 8/1/07 formatted as "Aug 07"

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

Thanks,

 

[njimack]

Basically, you need to return the first of the current month. I had to do something similar and used the following code...

Sub Navigate()
    Dim n As Long
    Dim Mth As Integer

    Mth = Month(Range("Navigation"))

    n = 4

    Do Until Month(Cells(2, n)) = Mth

        n = n + 1
    Loop

    Cells(2, n).Select


End Sub

 

[mvatoi]

I put the following in, and changed the range to "A4:AC4" or "R4C2:R4C29" but it does not work.
I can't use a range name b/c I have 50 pages of similar data. The macro will look for the same range "A4:AC4" in each page.

Also, both places where (2, n) appears, I tried 2 ways (leave it like it is, and replaced it with (2, 29) , but neither of these ways work. (29=AC)

Sub Navigate()
    Dim n As Long
    Dim Mth As Integer

    Mth = Month(Range("A4:AC4"))

    n = 4

    Do Until Month(Cells(2, n)) = Mth

        n = n + 1
    Loop

    Cells(2, n).Select


End Sub

Thanks,

 

[njimack]

The code I posted was an example, you need to tweak it to your needs. Give this a whirl...

Sub Navigate() 
    Dim n As Long 
    Dim Mth As Integer 

    Mth = Month(Date)

    n = 2 

    Do Until Month(Cells(4, n)) = Mth 

        n = n + 1 
    Loop 

    Cells(4, n).Select 


End Sub