I need a Macro...Please Help.!
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: I need a Macro...Please Help.!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     


    :::Please forget me for the topic, I didn't mean to sound demanding or something similar. I am asking you for your generous contribution and please accept my truly apologies if I upset you for my careless words.:::

    =)
    As you can see in the above graphic of my spreadsheet. I have to insert a TOTAL WEEK 1, TOTAL WEEK 2,etc. under every other SUNDAY for every month with the respectives formulas. Which SUM(from monday to sunday). And I realy spend to much time cuting and pasting and checking that all the SUM(weedays) are correct, due to the fact that all months are not the same or start on the same weekday.
    I am starting with excel and my experience with macros at this time is very poor and limited. I hope, you can help me.

    I am sorry for my bad grammar and/or spelling.

    Best regards,

    GUS


    [ This Message was edited by: GUS on 2002-03-14 22:15 ]

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Gus.

    Thanks for a stimulating formula exercise this morning . And yes, if I understand what you want to do, this can be accomplished with formulas and conditional formatting; no VBA is needed. These are fairly complex formulas and I will explain the best I can, but if you will e-mail me I'll send the sheet I created in solving this problem.

    Here goes:

    In cell A2 put =IF(LEFT(B2,4)="Week","Total",WEEKDAY(B2))
    and copy and paste down the column.

    In cell B2 put the date of the first of any month in your favorite date format.

    In cell B3 put =IF(LEFT(B2,4)="Week",B1+1,IF(WEEKDAY(B2)=1,"Week 1",B2+1)) and copy & paste down through B9.

    In B10 put =IF(LEFT(B9,4)="Week",B8+1,IF(WEEKDAY(B9)=1,"Week "&RIGHT(OFFSET(B10,-8,0),1)+1,B9+1)) and copy & paste down the column.

    Your quantity numbers go in column C just like your example.

    In D2 put =IF(A2="Total",SUM(INDIRECT("C2:C"&CELL("row",D2)-1)),"") and copy & paste through d9

    In D10 put =IF(A10="Total",SUM(C3:C9),"") and copy and paste down the column.

    I've moved the hours over to column E.

    Now for the conditional formatting:

    Click cell A2 and click FORMAT > CONDITIONAL FORMATTING. for Condition 1 choose Cell Value Is equal to "Total" and click format to set the background color to light gray and fon to bold. Click OK and then ADD>>. Set Condition 2 to Formula Is =OR(A2=1,A2=7) and format the background color to yellow. click Ok twice. Now Copy and Paste Special > formats down column A.

    Click on B2 and open the conditional formatting box. Condition 1 is Formula Is =$A2="Total". Format background light gray and font bold. Click ok twice. Copy and Paste Special > formats down Column B, C and D.

    You are now ready. enter any first of the month date in B2 and watch as your colors and total lines automaticly adjust.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Thank you very much Sir,
    You got mail.

    I will try the above now..

    GUS

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com