I am looking to create a macro that takes the current month as an input and then outputs certain formulas and column titles. Below is the example out what I am trying to do: Input Box or DropDown:

Please enter month: Jan (or perhaps a 1)Data (many rows):

Columns

A......E...........F........G.........Q

Name..Jan '09...Feb '09...Mar '09...Jan '10

1. A....12.........1........3.........55

2. B....14.........7.......14.........78

3. C....47........44........2..........5The months are based on 2 years of data (24 month period) and span columns E through AB. E representing Jan '09 (month: 1) and Q representing Jan '10 (month: 13).

I need this macro to take an input which in this example would be the current month (Jan '10) and compare it to last month (Dec '09) and (Jan '09).

The formulas needed aren't difficult they are as follows:

-Current Month vs Prior Month % Change (Jan '10 vs Dec '09)

-Current Month vs. Last Years Month % Change (Jan '10 vs Jan '09)

-Prior 12 Month Average (ie Feb '09 thru Feb '10)I would need this Macro to Create Column headings according to the Months it is comparing.

(ie. Jan10 vs Jan09 % Change)Please help, Thank you!

Hi, You are asking for a fairly extensive amount of Visual Basic code, or a lot of formulas.

I can offer you some suggestions to help you get to your solution, but not a complete solution.

I am suggesting formulas as these are likely to be more useful as building blocks for your solution.

1. Organize your data in rows with monthly headings in the top-most row. (Headings in columns E to AC for this example Jan-08 to Jan-10)

The headings are the 1st day of each month. Enter valid Excel dates - then format as "mmm-yy"

By using dates that Excel recognizes you will be able to use functions such as SUMPRODUCT() to find ranges of data, as well as being able to use HLOOKUP() to find data in specific columns.2. Use the headings in row 1 to create a drop-down list, for users to select a month.

In Cell A1 is the heading "Select Month"

Cell A2 is the cell used to select the month

I added a drop down list of months/years using the headings in cells E12 to AB1

Select cell A2 then from the Ribbon - Data - Data Validation - Data Validation - Allow: List and select cells E1 to AC1 as the Source:

Format cell A2 with "mmm-yy"3. As you want headings to reflect the selected month and current month, create two date texts that can be included in other headings.

In cell A4 Enter this formula:=TEXT(A2,"mmm-yy")

This will show the selected month/year

In cell A5 Enter this formula:=TEXT(NOW(),"mmm-yy")

This will show this month/year

If you want a heading in cell B6 for % Change use this formula= "Percentage Change" & CHAR(10) & $A$4 & " to " & $A$5

The CHAR(10) forces the date range on to a second line. Increase the row height and format the cell with text wrapping.4. Here are three formulas that will return useful building blocks for your formulas.

4a The difference between the data this month and the selected month =HLOOKUP(DATE(YEAR(NOW()),MONTH(NOW()),1),E1:AC3,2,FALSE)-HLOOKUP(DATE(YEAR(A2),MONTH(A2),1),E1:AC3,2,FALSE)

Note that this uses the first day of the month as the index to lookup in the HLOOKUP() function. For this to work the dates in row 1 must have been entered as the 1st of each month, even if formatted without the day showing.For data on subsequent rows increase the range E1:AC3 e.g. E1:AC10

and increase the row to get the data from e.g., HLOOKUP(DATE(YEAR(NOW()),MONTH(NOW()),1),E1:AC10,10,FALSE)4b The avarage of values between two dates (inclusive of the earlier month and exclusive of the current month)

=SUMPRODUCT(($E$1:$AC$1>=A2)*($E$1:$AC$1<=DATE(YEAR(NOW()),MONTH(NOW())-1,1))*($E$2:$AC$2))/SUMPRODUCT(($E$1:$AC$1>=A2)*($E$1:$AC$1<=DATE(YEAR(NOW()),MONTH(NOW())-1,1)))In the above formula

the first part selects only months equal to or later than the selected month

the second part selects only months less than last month [note the -1 after Month(now())]

and for months that meet both criteria it adds the values in row 2 - it does this because each cell that doesn't meet the date criteria returns zero which when multiplied by the value in row 2 returns zero. Cells that match the date criteria return 1 and this is multiplied by the value in row 2, returning the row 2 values -

this returns the total or sum for the months and this is divided by the number of months which are selected by the same process - all cells meeting the date criteria return 1, so when 12 months meet the criteria this returns 12.

This part of the formula can be used on its own to show the number of months selected.4c A heading using the month count formula:

="Average"&CHAR(10)&A4&" to "&$A$5&CHAR(10)&TEXT(SUMPRODUCT(($E$1:$AC$1>=A2)*($E$1:$AC$1<=DATE(YEAR(NOW()),MONTH(NOW())-1,1))),"#0") & " months"

If Feb-09 was selected this shows:"Average Feb-09 to Jan-10 11 months"

Hope this helps.I don't mind trying to answer specific issues that arise as you work through this.

Regards

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History