Method to get dates based on Month

  • Thread starter Thread starter ckramer7070
  • Start date Start date
C

ckramer7070

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
 
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
 
Back
Top