I have been working on a solution to my problem for some time. I have found a formula that works but gives a circular ref error. I would like to correct this error by using vba to accomplish the same task. I found a snipit of code which performs the general objective Im trying to achieve but have not been able to adapt it to my specific use. I figure I would consult the community and hopefully get some feedback on my method.
Here is the task im trying to complete. I have an excel sheet with a number of categories that relate to product information. I created a userform that individuals can interact with to submit data to this excel sheet. One of the functions of this userform is to log the date and time of the user submission. Each submission has followup activities which another user addresses at a later time. When all of these actions have been completed, I would like to calculate the total disposition time from submission to completion. Essentially I want to run a formula based on a cell value change. Here is the circular ref formula I was using:
The Log Complete column is an "IF" formula that displays "Yes" when some criteria has been met and display "No" until the criteria has been met. When the formula displays "Yes" I attempting to calculate the different between the date and time NOW() minus the date and time of the entry in hours.
Here is a reduced version of the excel sheet showing relevant cells:
Right not the code looks for a numerical change the Log Complete column. I would like this change to occur when rCell.Value = "Yes". I have been unsuccessful in completing this task. Additionally, I would like to modify the .Value = Now to actually equal (NOW()-Table1[Date and Time])*24. I believe I need to create a range and set to A:A to store date and time values.
Any help or alternative solutions would be greatly appreciated. I am continuing to work on this and will post updates as I find them. Thanks in advance.
Placed in the 'Throughput' column and custom format to: '[h]:mm' ? If you reference the 'Throughput' column with the formula in that column (as you have done), you'll get a circular reference as you describe.
Thanks for the feedback. I attempted the approach you describe and found the formula was continually reevaluated. This caused the throughput time to inflate as the value of NOW() is always changing. I'm looking more of a time stamp function when certain criteria has been met. Thank you for the suggestion.
Hi
Yes I didn't realize you wanted a Time Stamp. The formula approach will constantly update as you refresh the workbook. Do you just want your user form to enter a Time Stamp alongside the 'Yes' in the 'Throughput' column? You can easily add the Now-Date & Time calculation to the column alongside the 'Yes' entry from your user form when adding data, without running a Worksheet Event.
Yes that is what I’m trying to accomplish. The Throughput column should calculate Now-Date and Time when the Log Complete column updates to "Yes". My current formula give the circular ref described earlier. I was looking for an alternative solution and ran across the VBA code described above. Another alternative would be to add more columns to the form; one for a completion date and another for the calculation. It was my hope to perform these two steps in one go around to simply the form. I do like the idea of using VBA in order to prevent users from changing the code; which is an ongoing issue.
Do you suggest any other approaches to this dilemma? Thank you for taking the time to help.
Hi
I've put together a sample form which is perhaps how I might go about this. The worksheet has a Table and the user form adds date; time and yes or no with a calculation if 'Yes' is chosen. There's also a validation check for the dates and time input.
Hope it gives you a few ideas. Just to note, I'm still learning VBA myself so there may be a better way of doing this but try it out.
I'm wondering if the formula above functions correctly, should I even change it. I am reading that you can allow a circular reference by turning on iterative calculations and specifying the number of iterations. Since I only want the formula to run one time after the first IF condition is met, I should be able to set the iterations to one.
Hi
I'm still not sure why you need to even reference the 'Throughput Time' column with your formula. You also said a formula solution was no good as it updates with the sheet:
I attempted the approach you describe and found the formula was continually reevaluated.
That is why you wanted a VBA Time Stamp value which will not update each time the sheet is recalculated. Does my example form not give you any ideas? You said you were using a user form to submit data.
I reference the “Throughput Time” column as a means to only evaluate the NOW-Date & Time formula one time. For example, when the worksheet event calls for the formula to reevaluate, the Throughput formula checks itself to determine if a value has already been determined. If so, then do not reevaluate the formula. This was the circular reference. The formula looks to be under control since I turned on iterative calculation function.
I was looking for a slightly different approach than you offered in your code. I was attempting to create a range for the “Log Complete” column. This approach would trigger the NOW-Date & Time formula in the event any value within the range was equal to “Yes”. Additionally, I think I need to create ranges for the Date & Time column as well as Log Complete. However, I am having difficulty extracting the .Value of each cell within the range. If that makes since.
In your code, if I change the “Log Complete” value to “Yes”, the code does not execute. Ideally I believe VBA would be the most robust approach to this solution. Moving forward, I will continue to dig into this topic; if anything this will expand my knowledge on working on ranges. I do appreciate your help on the topic and please, if I’m misunderstanding your approach please enlighten me. I just hope I’m not overthinking the problem at hand. Again, thank you for your support thus far.
Hi
I guess I'm not understanding exactly what it is you're attempting. My User Form inserted a 'Yes' or 'No' into the 'Log Completed?' column and then the Time Stamp in the adjacent column. However, if you are manually entering 'Yes' or 'No' into your Table, then you would perhaps need a Worksheet Change Event to enter the Time Stamp.
See the attached file. If you type 'Yes' into the 'Log Completed?' column it enters the Time Stamp. However, if a Time Stamp already exits it does nothing. Typing 'No' or any other value will result in the Time Stamp being cleared.
DBY
Last edited by DBY; 07-28-2015 at 12:09 PM.
Reason: Uploaded New Example File
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks