Excel = Enter Static Date when Reaching 100% 2

[ladyck3]

I was in the MS OFFICE Forum and they lead me here.
I know how to get into the VBA window and paste code, that's my knowledge of VBA for the most part. I am in desperate need of assistance and I'm not a programmer. I know Excel very well up to the point of needing code and I freeze.

With these conditions, will you still help?

If so, here is my dilemma. D3 is the final SCORE or Percentage in a long line of calculations. When D3 turns or calculates to 100%, I need E3 to populate with a static date. This is for a scorecard of sorts and we need to know WHEN (not date/time) just the date... when D3 turns 100%.

I will apply conditional formatting at that time..

If D3 turned 100% on or before the date in B9, then E3 will turn GREEN, if it is after the date in B9, it will turn RED.

Knowing how all powerful VBA is, I'm sure there must be a way to do this.... I can do the formatting after I get the cell to hard code the date...

However, I believe if you are privy to all f these criteria and can do the date part, VBA can also handle the formatting, right?

If I am to naive for you to consider helping me with this, please help me to find a resource.

SINCERELY and humbly...


LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

Right-Click the Sheet Tab and select View Code

This put you in the Worksheet Code Window for the sheet that you want to code this event.

The EVENT is the Worksheet_Calculate event, since, I assume that D3 contains a FOLMULA and will not actually change.

Private Sub Worksheet_Calculate()
    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
End Sub

Skip,

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

 

[ladyck3]

<waving> HI SKIP!!!

Oh you are my hero, I mean it sincerely... thanks for the directions to the VBA Forum but even more...

THANK YOU this works MAGNIFICENTLY!!!

Thank you!!!!!

STARS ALL OVER FOR YOU

Not yelling, its excitement!



LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

See, that was pretty painless AND it can be pretty fool-proof, if you were to add some sheet protection to protect that E3 cell from being tampered with, if necessary.

Skip,

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

 

[ladyck3]

Very painless... but in my defense, I explained my inexperience.

I am linking these 2 cells to a master list. On the master I'm just =cellref but I want the same functionality or formatting so...when the master changes to 100 it too reflects the color coding.

SO...I pasted the code into the master worksheet and altered the D3 to K4:K150 and E3 to L4:L150 but as you <I'm sure you are slapping the palm of your hand up at your forehead> knows...this didn't work... SURPRISE..

Do I have to repeat as follows, one entry for each row of cells?

<CODE>
Private Sub Worksheet_Calculate()
If [K4].Value = 1 And [L4].Value = "" Then [L4].Value = Date
If [K5].Value = 1 And [L5].Value = "" Then [L5].Value = Date
End Sub


etc? Or a separate calc Private to End repeat of the original for each row? (I'll get this some day ... maybe)

Thanks!


LadyCK3
aka: Laurie

 

[SkipVought]

For a range...

Private Sub Worksheet_Calculate()
  Dim r as range

  for each r in Range("K4:K150")
     with r
       if .value = 1 and .offset(0,1).value = "" then .offset(0,1).value = Date 
     end with
  next
End Sub

Skip,

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

 

[SkipVought]

I think i'd also change disable events so that the calcualte event would not fire each time a new Date is inserted.

Private Sub Worksheet_Calculate()
  Dim r as range
  application.enableevents=false
  for each r in Range("K4:K150")
     with r
       if .value = 1 and .offset(0,1).value = "" then .offset(0,1).value = Date 
     end with
  next
  application.enableevents=true
End Sub

Skip,

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

 

[PHV]

You may try this:

Private Sub Worksheet_Calculate()
  For Each c In Range("K4:K150")
    If c.Value = 1 And c.Offset(0, 1).Value = "" Then c.Offset(0, 1).Value = Date
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ladyck3]

Thank you for the quick response... it works! But you knew that Thank you for the assistance.. baby steps


LadyCK3
aka: Laurie

 

[ladyck3]

Ok, Skip... in my ignorance...

You gave me this and it works

Private Sub Worksheet_Calculate()
If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
End Sub

Well I added this... but its not working so I'm wondering if its okie to have two like this?

Private Sub Worksheet_Calculate2()
If [E7].Value = 1 And [G7].Value = "" Then [G7].Value = Date
End Sub

The project was complete but now we have finished the first time and I see a need for modification.

Cells E7, E8, E9 each turn to 100% individually and they make up the total in D3 as the first code addresses.

However I now need for cells G7, G8 and G9 to enter the date statically as E3 does in the first code. SO... I modified the first code to change the cell references and changed the title to add a "2". I was going to repeat this (if it worked) for E8 and E9 but alas, the one for E7 does not work.

So please help.





LadyCK3
aka: Laurie

 

[SkipVought]

There is ONLY ONE Worksheet_Calculate event for any worksheet...

Private Sub Worksheet_Calculate()
    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date

    If [E7].Value = 1 And [G7].Value = "" Then [G7].Value = Date
End Sub

So you ONLY need these Dates on rows 3 & 7 and will NEVER need on other rows?


Skip,

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

 

[ladyck3]

We have three teams that comprise the 100%... thus if team 1 reaches 100% (33% of D3) on time, the date would be entered into G7 showing when E7 turned 100%.

Its on time...

HOWEVER, Teams 2 and 3 have not done any work yet so therefore they are late and for accountability purposes, we need the dates placed into cells G8 (team 2) and G9 (team 3) when they actually completed their tasks to show where the breakdown is.. obviously E3 (total project) will show late but at least Team 1 will be in the clear as their tasks were completed on time.

That's why I'm seeking dates in not only the project field but the fields for each of the 3 teams that comprise the project.

I will test your new code

Thanks!!

AWESOME and I added the two extra lines within that same calculate section and it worked! But again, you knew that

You're a PRINCE AMONG MEN Nah.. yer da KING!

Thanks!!!


LadyCK3
aka: Laurie

 

[ladyck3]

HELP on this....
This code:

Private Sub Worksheet_Calculate()
  Dim r as range

  for each r in Range("K4:K150")
     with r
       if .value = 1 and .offset(0,1).value = "" then .offset(0,1).value = Date 
     end with
  next
End Sub

Is not working... I'm getting a Run-time error '13' and the highlighted section is this:

if .value = 1 and .offset(0,1).value = "" then

I have no clue how to fix this. How this works is on the index page called Master_2010 is data which all links to all other worksheets in the workbook. This column is no different, it links to cell E3 in each work book.....

These cells are also included in the VBA Codes above but this one is breaking... any advise?

Thanks again, in advance....


LadyCK3
aka: Laurie

 

[SkipVought]

When this errors, hit the DEBUG button and determine what the CELL VALUE is at that instant. ie what ROW is the code on in column K and consequently what VALUE in column K?

Skip,

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

 

[SkipVought]

Laurie,

Did you COPY this code DIRECTLY from your Code Window? I think NOT!

Skip,

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

 

[ladyck3]

It shows if I hover over the yellow highlighted section, the time 12:00:00 AM but the data is in date form....

I think this was the problem? Something got out of whack... I change the format on that column to mm/dd/yyyy on and also for some reason one of the cell references was duplicated in the column so I fixed that... its working now.

Was the problem that caused the 13 error the fact that the data was in the wrong format?

LadyCK3
aka: Laurie

 

[SkipVought]

Exactly what OBJECT were you hovering over? The ENTIRE STATEMENT is yellow.

Also please answer my last post about WHERE you copied your code from.

Skip,

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

 

[ladyck3]

WHen I had the error (which is gone now but may break again so you are asking this "where" from???)

The option to end or debug came up... I chose Debug and it opened the code window.... or that's what I call it, is that the proper term?

This line was present...

if .value = 1 and .offset(0,1).value = "" then .offset(0,1).value = Date

The section in bold is the part that was formatted.

As I'm typing (sorry I do have a crappy memory) the error 13 that I got did stay Mismatched Type. I see above it does signify date which is correct.

YOu said to me

When this errors, hit the DEBUG button and determine what the CELL VALUE is at that instant. ie what ROW is the code on in column K and consequently what VALUE in column K?

I went ot the code window (when I hit debug) and the section above was highlighted... I did not know what you meant about finding what row... because this should be running on all rows from K4 to K150 but when I hovered over the highlighted (bold above) section it showed the value was 12:00:00 AM.

This column is data provided via a link to E3 on a specific worksheet so by default it showed "0" as the cell value... I formatted the cells to be date format mm/dd/yyyy and then conditionally formatted it so that if the cell =0 the text would be white so the cells appear empty.

When the date appears (because one of the spreadsheets has reached 100%) additional formatting is applied... if it is before or equal so the date cell on that page then the date turns green, but if it is after the date, the date turns red.

By hovering over the highlighted area is that where I should have gone to figure out what you were asking me? I saw no location (row/column) anywhere.



LadyCK3
aka: Laurie

 

[SkipVought]

faq707-4594

What you do is us the With r statement, looking at the r object in the watch window. This is the way to examine the ROW & COLUMN and VALUE. knowing the ROW & COLUMN, yoju can, during the debug, switch to the SHEET and scroll to that location to observe what's on the worksheet.

Skip,

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

 

[ladyck3]

Oh.. umm I read the article you suggested... but its a bit more "programmy" than I am....

Since its working now I have no way of being able to find out what row and do as you ask but.... without finding the exact location... was my deduction valid?

Hovering over the highlighted section of the code in the debug window... it showed time format for a date instruction... so is it safe to assume that making sure that all of the data was truly in date format, may have been the fix? One of the cells may have been in tiem format rather than date?

I know, hard to predict since its working right?

It is working now.... that's a good thing
I'd like to know what happened to break it as well but I don't know how it broke in the first place.


LadyCK3
aka: Laurie