Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 23rd, 2007, 12:19 PM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Macro FIND THIS MONTH in a given range

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
Reply With Quote
  #2 (permalink)  
Old August 24th, 2007, 03:20 AM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old August 24th, 2007, 03:25 AM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

supplement to my reply

change
dim datetofind as integer


Reply With Quote
  #4 (permalink)  
Old August 24th, 2007, 08:12 AM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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,



Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using month & year variable to get date in range rsearing ASP.NET 2.0 Professional 2 July 5th, 2008 08:30 AM
Finding nday of evry month within date range arnabghosh Classic ASP Professional 0 December 6th, 2007 12:42 AM
find days per month in a date range rojer MySQL 1 June 5th, 2007 07:36 PM
Excel Formula End Range Changes when Macro Is Run maaron Excel VBA 0 October 11th, 2005 10:56 AM
Excel Formula End Range Changes when Macro Is Run maaron Beginning VB 6 0 October 11th, 2005 10:33 AM



All times are GMT -4. The time now is 02:47 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.