Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method to get dates based on Month
Is there any way to have a for/next loop to search a Column for
months? One specific column has inventory number information as well as date separators, the dates are in "mmm-yy" format. Sub FindDate() Dim FoundCell As Range Dim Mnth As Variant Range("InvNum").Select Mnth = ? For Mnth = Jan To Dec With Sheets("Job Sheet") Set FoundCell = .Cells.Find(What:=Mnth, _ After:=.Range("C2"), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder: _ =xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not FoundCell Is Nothing Then .Activate FoundCell.Select End If End With Next Mnth End Sub This is just testing code, I have many other actions waiting in anther routine I need to plug in after this gets done. Thanks for any help, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method to get dates based on Month
Something like this should do it...
Public Sub FindMonths() Dim rngFound As Range Dim rngFoundAll As Range Dim strMonth As String Dim lngCounter As Long Dim rngToSearch As Range Set rngToSearch = Sheets("Job Sheet").Cells For lngCounter = 1 To 12 strMonth = Format(DateSerial(2007, lngCounter, 1), "Mmm") Set rngFound = FindOneMonth(strMonth, rngToSearch) If Not rngFound Is Nothing Then If rngFoundAll Is Nothing Then Set rngFoundAll = rngFound Else Set rngFoundAll = Union(rngFound, rngFoundAll) End If End If Next lngCounter If Not rngFoundAll Is Nothing Then rngFoundAll.Select End Sub Private Function FindOneMonth(ByVal strMonth As String, _ ByRef rngToSearch As Range) As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Set FindOneMonth = Nothing Set rngFound = rngToSearch.Find(What:=strMonth, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress Set FindOneMonth = rngFoundAll End If End Function -- HTH... Jim Thomlinson " wrote: Is there any way to have a for/next loop to search a Column for months? One specific column has inventory number information as well as date separators, the dates are in "mmm-yy" format. Sub FindDate() Dim FoundCell As Range Dim Mnth As Variant Range("InvNum").Select Mnth = ? For Mnth = Jan To Dec With Sheets("Job Sheet") Set FoundCell = .Cells.Find(What:=Mnth, _ After:=.Range("C2"), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder: _ =xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not FoundCell Is Nothing Then .Activate FoundCell.Select End If End With Next Mnth End Sub This is just testing code, I have many other actions waiting in anther routine I need to plug in after this gets done. Thanks for any help, Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method to get dates based on Month
On Feb 20, 12:17 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Something like this should do it... Than a ton Jim, your code worked perfectly Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Revenue based on dates within a month | Excel Worksheet Functions | |||
Return dates based on month and day of week | Excel Worksheet Functions | |||
Dates - Several Days In a month to month only | Excel Discussion (Misc queries) | |||
Fill column with dates of month depending on month in A1 | Excel Programming | |||
How can i filter dates based on day of month | Excel Worksheet Functions |