how to subtract a set value from a cell when a another cell records a value of 1

Hi and welcome to the forum

Im assuming this can only be done with VB code

You assume correctly, a cell can either contain data (text/value) or a formula, it cannot contain both

My first suggestion is attach a sample workbook, while you give some ideas of the cells being referenced, a sample workbook would give us a clearer picture of what YOU would like to see the results as:
To Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I'm not sure what the problem is, unless you want to change the formula a bit :
=INT(I4/7.6)&"-"&ROUND(MOD(I4,7.6),1)
other than that, the formulas are displaying exactly what you are describing, Days & Parts of Days, the reason the round() works better, is that it cuts of the level of precision at 1 decimal point, your original formulas went full prcision, which probably added a little bit of (UNAVOIDABLE) roundof error at the 12th or 13th or 14th decimal, if you had expanded the row width till the '###..." had dis-appeared, you probably would have caught it your self

J4:

Please Login or Register  to view this content.

In post #1 you said: "... I dont want any formula in cell I4 because this cell is required to enter values as required. ...". It was my understanding that you wanted J4 to convert the hours in I to 7.6 hr days. It seems like you are asking to be able to manually put some value in J4 and have a VBA function to convert that to ??? Are you manually filling I4 or J4 and with what values?

The formula =ROUND((I4-F10*7.6)/7.6,1)in J4 subtracts multiples of 7.6 from the value in I4 based on the number in F10. If F10 is zero, 0 is subtracted. If F10=2, 15.2 is subtracted, etc. The remainder of the subtraction is divided by 7.6 to convert the hours to 7.6 hour days. The number, 100 in this case, remains in I4.

An alternative:

F10 = 3, you enter 100 in I4, and a VBA cell change is triggered which multiplies 7.6 by 3, subtracts 22.8 from 100 and puts 77.2 back in I4. THEN, the formula in J4, or the VBA, just divides 77.2 by 7.6 (10.16 days in J4) . Notice that the original 100 in I4 is lost. Is this what you are looking for?

Do you want the original 100 in I4 replaced by VB?

Try this solution:
This is the worksheet change event handler. It will call the actual calculation code below if you make a change in I4,
I5 or Column E

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Right click the selected sheet's tab
4. Choose View Code
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

---

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

1. In your workbook, you have an existing sheet change event handler Developed by Contextures Inc. When you copied mine in, you changed the name to Worksheet_Changes. The event handler names are part of Excel, you can't change or modify the names. So, when you made the changes Excel never saw the new procedure. Delete the s from the name and either comment out or delete the code from Contextures. You can have only one Private Sub Worksheet_Change(ByVal Target As Range) per sheet.

2. In my Private Sub Worksheet_Change(ByVal Target As Range), the lines

If Not Intersect(Target, Range("I4:i5")) Is Nothing Then

Call ConvertToDays(ActiveSheet, Target.Row)

check if the changed cell was either I4 or I5.

If it is TRUE that the changed cell is I4 or I5, it branches the macro ConvertToDays passing it a pointer to the sheet to use, in this case "TEMPLATE", and the row on that sheet, either row 4 or row 5.

If it is FALSE that the changed cell is I4 or I5 then it checks to see if the changed cell is in column E cells 22 to 418 (because those are the cells you had data validation in on the sample you posted). If one of those cells was changed, then it checks to see if you selected A\Leave or SICK. If you did not then the macro does nothing and exits.
However, if A\Leave is selected,it branches the macro ConvertToDays pointing to TEMPLATE/row 4, etc.