Need VBA help Please

[PMasters28]

I don't know if I am going to explain this correctly but I am going to try. I am trying to help out my boyfriend by making it easier for him to complete his daily progress reports on the computer for his construction job. The VBA code that I need to create needs to control the date and the day of the week when you change the project day. For example when I type in 28 next to the blocks that ready Day:__ of 291 it needs to change the date and the day of the week. If anyone can help with this it would be very helpful. I have found a VBA to change the day of the week when you change the date so I know that I am halfway there I just need to be able to input the day of the project and it change it all. The reason for this is because it will make this portion of his paperwork quicker and allow him more time for paperwork that is more pressing. If anyone can help it would be very much appreciated. I included a link to the example of the excel workbook. If anyone could help that would be great just found out I originally posted this in the wrong spot.

http://www.mediafire.com/?xxsgloa6q5zpo0d

 

[PMasters28]

This explains why I need a VBA code that when I change the day of 291 I need it to change the day of the week and the date. The project is supposed to take 291 total days. The start date of the project was 09/26/2011 and the end date is 07/15/2012.

 

[UnsolvedCoding]

I am not sure I entirely understand what you want but here are some ways to deal with dates. These all use the systems date, so if the system isn't right it will screw things up.

For these examples I am using todays date of 10-25-11 with the current time from my time zone.

Now - gives you the current calendar date and time. Example 10/25/2011 8:52:29 AM

Date - gives the current calendar date. Example 10/25/2011

Year(Date) - gives the current calendar year. Example 2011

Month(Date) - gives the current calendar month. Example 10

Day(Date) - gives the current calendar day. Example 25.

If you want to go forward in time do something like date + 7 which would go out 7 days from todays for a date giving you 11-1-2011

If you want to check you can open your immediate window (control G) and type ?Date and then hit enter.

 

[PMasters28]

I don't know if you opened up the excel workbook that I included but at the top it says Day: of Day further down in the workbook I have the weekday and then beside that I have the date. I know the VBA to deal with the date and day of the week but I am wanting one that when I enter a number in the Day: of day area it changes the rest.

 

[UnsolvedCoding]

I can't download, the company has saftey protocols in place to prohibit downloads.

OK, so using your start date of 9-26-11 set your formula as now + the day.

Example
Dim Startdate
Range("A1").value = Startdate

Assume cell B1 says "Day of" Cell C1 is where the day number is entered and cell D1 says "of 291"

Try something like this
Range("A3").value = startdate + (Range("C1").value)

 

[PMasters28]

Just tried and it did not work. The cells that I am working with are cell A9 "Day of", Cell B9 is where the day number is entered and cell C9 says "of 291". When I enter an number in B9 I want it to change Cell D12 which is the day of the week (Monday thru Friday) and then also change cell G12 which is the current date. I can get cells D12 and G12 to work together but to make it easier on me I want to enter a number in B9 and it control cells D12 and G12 as well so we do not have to refer to a calendar with written in numbers for the project day. Sometimes there is not enough time for that and it would make it so much easier to fill out paperwork if I could enter one number into the page instead of multiple numbers.

 

[UnsolvedCoding]

Lets look at this a different way.

To get the days lapsed, instead of manually entering it have the code update it for you. Use todays date and subtract the start date from that, the number goes into cell B9.

Then take todays date and using weekday(date) update cell D12. Today would be 3 because the week starts on Sunday. You will have to write code to say that 3 = "Tuesday" and so on.

Then have cell G12 = Date()

Write all that into one sub and it won't matter what formula's are used.

That should give you all three without problems and then trigger it to run when a button is clicked.

 

[SkipVought]

This seems to be much ado about very little.

Let's assume
[tt]
A1: 9/26/2011
A2: 291
A3: =A1+A2 which is 7/13/2012
[/tt]
I don't know where your get 7/15/2012???

So the day 28 is simply, assuming the A4 contains 28
[tt]
A4: 28
A5: =A1+A4 which is 10/24/2011
[/tt]
and the day of week is simply...
[tt]
A6: =A5 formatted as "ddd" in CUSTOM displays Mon
[/tt]
That's ALL it takes!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PMasters28]

It should be 294. It was a typo. If you go from 9/26/2011-07/15/2011 it is 294 days. Since 2012 is a leap year. Like I said earlier I am wanting to be able to enter one number into the workbook and then it change the day of the week and the date. The only number I want to have to enter is the Day of: of 294. If this can be done please let me know.

 

[PMasters28]

Also if you look into my earlier posts I gave the cells that I am working with.

 

[SkipVought]

Some of your description sounds convoluted. The essential data elements are:
[tt]
[Project Start Date]
[Project Duration]
[/tt]
Everyting else is derived, is it not?

For instance the "Day ___ of" is simply
[tt]
=TODAY()-[Project Start Date]
[/tt]
The [Project End Date] is
[tt]
=[Project Start Date]+[Project Duration]
[/tt]
Once you enter the start and duration, there is nothing else to enter to status the Day ___ of and DOW!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PMasters28]

@unsolved if you could please help me out with that I am not sure I understand what you are talking about. Are you suggesting that when I enter the date it will change everything if so that is great. But I am not sure I understand how you got from point a to point b by your explanation. If you could give me an example that would be very helpful. Thank you.

 

[SkipVought]

If this can be done please let me know.

As posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PMasters28]

Skip that I understand. I know when the project start date is and the duration of the project. But when I enter the information for one cell I want it to fill in the information for the remaining two cells. that is where i think you are not understanding me. I want to be able to write one process that when I enter information in B9 it fills in D12 and G12 automatically.

 

[SkipVought]

Not only as posted, but performed all above calculations in less then 60 seconds!

I am a HUGE VBA advocate, but your requirements are extremely simple and exactly what spreadsheet function easily can achieve. You seem to want a Rube Goldberg solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Not only as posted, but performed all above calculations in less then 60 seconds!

I am a HUGE VBA advocate, but your requirements are extremely simple and exactly what spreadsheet function easily can achieve. You seem to want a Rube Goldberg solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The "AUTOMATICALLY" is a FORMULA. Simply a FORMULA!

Do you understand the power of the tool (Excel) in your hand?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PMasters28]

Ok so explain what you are trying to tell me because it sounds like what you are doing is only going to control two of the cells that I am working with. I can already do that. I want to control all three. the cells that I am working with are B9 which is the day of cell, cell D12 which is the day of the week and cell G12 which is the date. You are telling me by simply doing this:


[Project Start Date]
[Project Duration]

Everyting else is derived, is it not?

For instance the "Day ___ of" is simply

=TODAY()-[Project Start Date]

The [Project End Date] is

=[Project Start Date]+[Project Duration]

Once you enter the start and duration, there is nothing else to enter to status the Day ___ of

then it will fill in the day of the week and the date just by putting in the Day ___ of 294???

 

[UnsolvedCoding]

The main difference between what Skip and I are doing is that my method is for coding and his is to directly enter formulas into the cells to do the calculations.

I am working on example for you and will post it in a moment.

 

[PMasters28]

But see that is the part you are not explaining to me. I am trying to get help and you are telling me everything in terms I do not understand. I am not that versed in Excel. I know the basics but the basics are not going to get me what I need or want. I am asking for help and you are not explaining. If you would explain yourself a little better I would not be getting frustrated. But instead I am looking at this and not seeing what you are trying to show me.