Insert a formula with relative cell reference based on a number in a column

Hello,

I would love it if somebody smarter than me could help with this. I need a macro. I have 2 columns A & B. In column A I have set amounts that don't need to change. In column B each cell adds the cell above it and the cell to the left in column A.

Certain cells in column B have a number 1-12. I need to input 1 of 12 different formulas based on what number is in the cell, without changing any of the other cells beyond adding the new amount.

I'm not very good with VBA yet so all my attempts at cooking something up are failing miserably.

If it would help I will upload a sample of what I'm talking about. Any help would be greatly appreciated.

If I understand your post correctly you want the formula to calculate the value of cell B2 based on what value is in Cell B1 using the value of cell B1 and A2.

Have your code cycle through all the populated cells in column B and have it determine which of your 12 formulas to use based on the value in column one row up.

Give this a try and see if it's what you are after.

Please Login or Register  to view this content.

HTH

Sorry, I knew I wasn't explaining that very well.

Column B has a wide range of numbers, but I only need to input one of the 12 formulas if the number in the active cell in column B is between 1-12.

The number 1-12 in the active cell will determine what formula is entered. Then it just needs to continue doing that until it gets to the end of the sheet. If the value in the cell is not a number 1-12 it needs to leave the cell alone. Thanks a lot for the help.

Figured it out! Thanks so much. What you posted wasn't exactly what I wanted but with a little tweaking I got it to work. Thanks again for the help.

Should be there now. Forgot initially.

Since you have the date in column B why not just have the macro determine the interval and use that in the calculation?
That would eleminate the Month and Change columns from your sheet and you also would not have to manually go through and enter if the month has changed.

That's the only way I could figure out how to get the macro to work the way I needed it too. I'm not very good with VBA; I'm much better with formulas in the actual worksheet. Thus, I did as much as I could in the spreadsheet then I automated the rest.

Regardless I've got it to a point now where I only have to do the last part I detailed in the example. This is slightly more manageable to do manually. Thanks for the work you put into helping me, I really appreciate it.