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
|