Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
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 -- Linda |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
If really dates, try this. You may want to change the year??
=MATCH(DATE(2007,11,1),e:e,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "mathel" wrote in message ... 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 -- Linda |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
Sub FindMonth()
Dim rng As Range Dim intMnth As Integer Range("E1").EntireColumn.SpecialCells(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
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 -- Linda "Smallweed" wrote: Sub FindMonth() Dim rng As Range Dim intMnth As Integer Range("E1").EntireColumn.SpecialCells(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
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. -- Linda "Don Guillett" wrote: If really dates, try this. You may want to change the year?? =MATCH(DATE(2007,11,1),e:e,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "mathel" wrote in message ... 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 -- Linda |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
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(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda "Smallweed" wrote: Sub FindMonth() Dim rng As Range Dim intMnth As Integer Range("E1").EntireColumn.SpecialCells(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on current month
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! -- Linda "Smallweed" wrote: 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(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda "Smallweed" wrote: Sub FindMonth() Dim rng As Range Dim intMnth As Integer Range("E1").EntireColumn.SpecialCells(xlCellTypeCo nstants, 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 "mathel" wrote: 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 -- Linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Year-to-date based on current month | Excel Discussion (Misc queries) | |||
Finding values based on current month | Excel Worksheet Functions | |||
Finding values based on current month | Excel Programming | |||
Insert Criteria Based on Current Month | Excel Worksheet Functions | |||
Current date formula based on month | Excel Discussion (Misc queries) |