Find a cell based on current month

[Guest]

In Column E in a workbook I have cells with a month in it, formatted 'mmm'.
There will not be a cell for every month of the year. For example, one cell
could be Jan, the other May, Aug, etc.

Using VBA, is it possible to look for the current month in the column, if it
doesn't exist, look for the previous month until it finds the most recent.
IE: Look for Nov, if Nov doesn't exist, find Oct, if Oct not found, look for
Sep?

Thanks for any help

 

[Don Guillett]

If really dates, try this. You may want to change the year??
=MATCH(DATE(2007,11,1),e:e,1)

 

[Guest]

Sub FindMonth()
Dim rng As Range
Dim intMnth As Integer
Range("E1").EntireColumn.SpecialCells(xlCellTypeConstants, 23).Select
intMnth = Month(Date)
Do Until intMnth = 0
For Each rng In Selection
If Month(rng.Value) = intMnth Then
rng.Select
Exit Do
End If
Next
intMnth = intMnth - 1
Loop
If intMnth = 0 Then MsgBox "Cannot find month before current month this
year"
End Sub

 

[Guest]

I've copied the code exactly as written below and get a Run-Time Error '13'
Type Mismatch. The Debugger is highlighting the line in the code: If
Month(rng.Value) = intMnth Then

Also, when I looked at the worksheet, not all of Column E is highlighted for
the search, only certain portions of it. IE: The first 6 Rows in column E
were skipped, 9 rows highlighted, then 14 skipped, 13 row were hightlighted,
then 22 rows skipped.

Sorry, but I have no clue when it comes to VBA and understanding it. Any
suggestions as to what is going wrong?

Thanks

 

[Guest]

I would prefer not to 'hard code' with a year. The workbook has data pasted
into it throughout the year, then is save in a specific folder. The
following year, a new workbook is started, and it will be this workbook I am
searching through (the year would then be 2008), and so on.

The last time I worked on programming was in Lotus 1-2-3 and the department
used the set of files I created for 15 years! If I'm not around, no one
would know what to do to change the year in VBA.

 

[Guest]

Ok, I get it: I assumed when you said you had months formatted "mmm" that you
had actual dates with this format on the cells (e.g. 1/1/07 in a cell for
Jan) - I expect you actually have just typed ordinary text? And the reason
only portions of the column are selected is that my code only looks at cells
with something in to make it run faster.

Try this:

Sub FindMonth()
Dim rng As Range
Dim strMonths(12) As String
Dim str As String
Dim intMnth As Integer
Dim intCtr As Integer
For intCtr = 1 To 12
strMonths(intCtr) = Format(DateSerial(Year(Date), intCtr, 1), "mmm")
If strMonths(intCtr) = Format(DateSerial(Year(Date), Month(Date),
1), "mmm") Then
intMnth = intCtr
End If
Next
Range("E1").EntireColumn.SpecialCells(xlCellTypeConstants, 23).Select
Do Until intMnth = 0
For Each rng In Selection
If rng.Value = strMonths(intMnth) Then
rng.Select
Exit Do
End If
Next
intMnth = intMnth - 1
Loop
If intMnth = 0 Then MsgBox "Cannot find month before current month this
year", vbCritical
End Sub

 

[Guest]

This seems to be working perfectly.

Thanks for your help, I could not have done this work without so much help
from this web site!

 

[Don Guillett]

try this
=MATCH(DATE(year(today()),11,1),e:e,1)