Hello Everyone,
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:
2015-07-25_10-03-19.jpg
Here is the VBA I'm trying to modify.
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.
Bookmarks