+ Reply to Thread
Results 1 to 8 of 8

Looking for a macro that copies a formula from column to column as I change the month

  1. #1
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269

    Looking for a macro that copies a formula from column to column as I change the month

    Hi, I was hoping I could get another macro that copies a formula into a cell based on the month, but only when I tell it to make that change. I have included a small worksheet to illustrate what I am doing, but it is basically this. We want to put the actual cost into the “current” row as the months move on through the year. I have a special Vlookup formula already created that does this for me, but I do not want that formula in the “July” column when I am working in this file in March. What I am trying to say is, if it is the actual month of March then I want the March column to have my formula but not April – December. When it is April I will change the month word to April, then I will go and run this macro that over writes what is in April and puts in my formula in April. It does not matter if every time it puts the formula in it over writes it in the previous months formulas, as it will be the same formula, but I want to avoid writing over the future months formulas.

    The formula I need copied is this:

    =VLOOKUP($B4&$BX$18,Actuals!$C$3:$P$10,Actuals!D$1,FALSE)

    Thank you, thank you so much for any help you guys can share, I am most appreciative of the help I have received on this site.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    hi ,

    not sure if this is what you want but try this

    steve
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269
    Hi, thanks a ton for the help!! A couple of things... I can't see the macro for some reason. It won't let me go into visual basic to view the code. Is something protected?

    The other thing is when i put in february it copies over column C from actuals tab, March copies D, etc. January takes B from actuals. Jan should take E, Feb F, March G, etc.

    But this looks like it is working great, I would like to see the code so I can use it. Hey, is there a way to make it do both the price and the cost sheet with one click? We actually have 7 sheets to do it on.

  4. #4
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269
    Ok, I was able to see the code, do I need to write it that many times? We actually have 7 sheets with 30 lines each to fill in. Is the way you have it doing a copy, paste special formulas? The way the formula is written it can be "paste special formuls" into any of those green cells and beause of the $ it will read exactly what it is supposed to. So I am wondering if there is a way for it to just paste that formula in there without haveing to write it several times in visual basic.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You don't need a macro to do this. Use dates instead of text in the cells and then use this formula. Format the dates as "mmmm"

    =IF(MONTH(H3)<=MONTH($B$2),VLOOKUP($B4&$BX$18,Actuals!$C$3:$P$10,Actuals!E$1,FALSE),"")

    See the first row apples.
    Attached Files Attached Files
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269
    Quote Originally Posted by royUK
    You don't need a macro to do this. Use dates instead of text in the cells and then use this formula. Format the dates as "mmmm"

    =IF(MONTH(H3)<=MONTH($B$2),VLOOKUP($B4&$BX$18,Actuals!$C$3:$P$10,Actuals!E$1,FALSE),"")

    See the first row apples.
    That is very cool, but the problem is if someone fools around with the month and inputs the wrong month before its time it erases formulas that need to be in the outmonths.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Changing the month in B" does not erase any formulas, using the macro will be less flexible. By using the formula it is possible to look at an earlier month.

  8. #8
    Forum Contributor
    Join Date
    08-03-2005
    Posts
    269
    Quote Originally Posted by royUK
    Changing the month in B" does not erase any formulas, using the macro will be less flexible. By using the formula it is possible to look at an earlier month.
    You are right, and I started to think it would work, because it is an "if" statment and so it would put the right formula back in if they screw it up. But the problem is on 3 of my 7 tabs there are no formulas at all in the current row these are cells that have to be entered by hand so for those tabs I need a marco to stick the formula in after the months actuals come out. Really appreciate the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1