Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
Hi. I am trying to create a budget in excel. in the first row, i have the
year. in the second row, i have the months of the year. what i would like to do is the following: I would like to enter a line item for the budget (e.g., license fee). next to that, i would like to list the amount, followed by the month and the year that it should appear in the budget. e.g., license fee.....$10,000......July......2007 is there a command/macro that I can use that will take the dollar amount and place it in the right cell in the budget that corresponds to the month and year listed? my goal is to not have to manually move dollar amounts around as timing changes during the budget development process. Thanks for any suggestions. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
How is this different than the other post you just submitted?
-- Don Guillett SalesAid Software "CMD" wrote in message ... Hi. I am trying to create a budget in excel. in the first row, i have the year. in the second row, i have the months of the year. what i would like to do is the following: I would like to enter a line item for the budget (e.g., license fee). next to that, i would like to list the amount, followed by the month and the year that it should appear in the budget. e.g., license fee.....$10,000......July......2007 is there a command/macro that I can use that will take the dollar amount and place it in the right cell in the budget that corresponds to the month and year listed? my goal is to not have to manually move dollar amounts around as timing changes during the budget development process. Thanks for any suggestions. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
Yes. this pseudo code should give you some ideas:
Dim rng as range, rng1 as Range Dim cell as Range, rngMonth as Range Dim rngCat as Range, res as Variant Dim res1 as Variant With worksheets("Vertical_List") set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown)) End with with worksheets("Months") set rngMonth = .Range("B2").Resize(1,12) set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown)) End with for each cell in rng res = application.Match(cell.offset(0,2),rngMonth,0) res1 = application.Match(cell,rngCat,0) if not iserror(res) and not iserror(res1) then set rng1 = rngMonth(1,res) set rng2 = rngCat(res1) With worksheets("Months") .cells(rng2.row, rng1.column).Value = cell.offset(0,1) End with end if Next If multiple years are involved, that is not evident in your question. -- Regards, Tom Ogilvy "CMD" wrote: Hi. I am trying to create a budget in excel. in the first row, i have the year. in the second row, i have the months of the year. what i would like to do is the following: I would like to enter a line item for the budget (e.g., license fee). next to that, i would like to list the amount, followed by the month and the year that it should appear in the budget. e.g., license fee.....$10,000......July......2007 is there a command/macro that I can use that will take the dollar amount and place it in the right cell in the budget that corresponds to the month and year listed? my goal is to not have to manually move dollar amounts around as timing changes during the budget development process. Thanks for any suggestions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
Thanks, Tom. Yes, multiple years would be involved. That is what makes it
so complicated doing it manually...I have about 200 line items that spread out over 5 years. Chris "Tom Ogilvy" wrote: Yes. this pseudo code should give you some ideas: Dim rng as range, rng1 as Range Dim cell as Range, rngMonth as Range Dim rngCat as Range, res as Variant Dim res1 as Variant With worksheets("Vertical_List") set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown)) End with with worksheets("Months") set rngMonth = .Range("B2").Resize(1,12) set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown)) End with for each cell in rng res = application.Match(cell.offset(0,2),rngMonth,0) res1 = application.Match(cell,rngCat,0) if not iserror(res) and not iserror(res1) then set rng1 = rngMonth(1,res) set rng2 = rngCat(res1) With worksheets("Months") .cells(rng2.row, rng1.column).Value = cell.offset(0,1) End with end if Next If multiple years are involved, that is not evident in your question. -- Regards, Tom Ogilvy "CMD" wrote: Hi. I am trying to create a budget in excel. in the first row, i have the year. in the second row, i have the months of the year. what i would like to do is the following: I would like to enter a line item for the budget (e.g., license fee). next to that, i would like to list the amount, followed by the month and the year that it should appear in the budget. e.g., license fee.....$10,000......July......2007 is there a command/macro that I can use that will take the dollar amount and place it in the right cell in the budget that corresponds to the month and year listed? my goal is to not have to manually move dollar amounts around as timing changes during the budget development process. Thanks for any suggestions. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
Sounds like you don't ahve categories on the left as I assumed, so I suggest
you just select your data and create a pivot Table. Data=Pivot Table -- Regards, Tom Ogilvy "CMD" wrote in message ... Thanks, Tom. Yes, multiple years would be involved. That is what makes it so complicated doing it manually...I have about 200 line items that spread out over 5 years. Chris "Tom Ogilvy" wrote: Yes. this pseudo code should give you some ideas: Dim rng as range, rng1 as Range Dim cell as Range, rngMonth as Range Dim rngCat as Range, res as Variant Dim res1 as Variant With worksheets("Vertical_List") set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown)) End with with worksheets("Months") set rngMonth = .Range("B2").Resize(1,12) set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown)) End with for each cell in rng res = application.Match(cell.offset(0,2),rngMonth,0) res1 = application.Match(cell,rngCat,0) if not iserror(res) and not iserror(res1) then set rng1 = rngMonth(1,res) set rng2 = rngCat(res1) With worksheets("Months") .cells(rng2.row, rng1.column).Value = cell.offset(0,1) End with end if Next If multiple years are involved, that is not evident in your question. -- Regards, Tom Ogilvy "CMD" wrote: Hi. I am trying to create a budget in excel. in the first row, i have the year. in the second row, i have the months of the year. what i would like to do is the following: I would like to enter a line item for the budget (e.g., license fee). next to that, i would like to list the amount, followed by the month and the year that it should appear in the budget. e.g., license fee.....$10,000......July......2007 is there a command/macro that I can use that will take the dollar amount and place it in the right cell in the budget that corresponds to the month and year listed? my goal is to not have to manually move dollar amounts around as timing changes during the budget development process. Thanks for any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro or formula | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Add formula to Macro??? | New Users to Excel | |||
Do I need a formula or Macro? | Excel Worksheet Functions | |||
Macro with in a formula | Excel Programming |