I would like to either populate a cell value from direct key input or have the cell value derived from a formula.

[delos]

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.

D6 is the first OFF on image. I have tried using this formula i got from another forum and modified it for what you see above, but when i use letters like OFF it doesn't work can someone help me with this.
Also K6 is a hidden cell with the formula in it, and i refer to it in L6 as =K6. L6 is the cell with answer and ability to over ride with key input. The hours and days off have data validation with drop downs on them to make things simpler. I would like to apply the vba formula below to all of L column starting on L6 all the way down so that when the formula in K6 which is =COUNTIF(D6:J6,">-1")*8, gives the answer using formula or ability to manually input another #.
<code>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("D6:J6")) Is Nothing Then
If WorksheetFunction.Count(Range("D6:J6")) = 7 Then
Range("L6").Formula = "=K6"
End If
End If

End Sub

I don't know how to program so I don't think above vba is right.
Any help please!
</code>

 

[My Aswer Is This]

I would like to help but I need a more clear explanation of what your attempting to do. I'm sure it's my fault and should understand but I do not.

 

[delos]

I would like to help but I need a more clear explanation of what your attempting to do. I'm sure it's my fault and should understand but I do not.

Thank you for trying to help me.

Starting on D6:J6 going across are the hours and days off, 7 cells total. On some of the cells it will have text entries like OFF or number's like their start time. On L6 will be the total hours they work for that week. I used the formula =COUNTIF(D6:J6,">-1")*8 on K6 and hid that cell that way it won't be overwritten. L6 will refer to it by using the formula =K6. When text is input on cell D6:J6 countif won't count it because it is not greater than -1 but if you put a number greater than 0 which would be midnight time wise, 1 will be 1am and 1700 will be 5pm and so on, it will count the cells. So if there are 5 cells with numbers it will multiply it by 8 to give you 40hrs in L6 the text wont be counted. So for each row there will be 7 cells 5 will have numbers and 2 will have text. On L6 the formula will be =K6. I would like to manually adjust the 40hrs to 45hrs if they 5hrs overtime, but when i do that the formula =K6 will be erased, so now 45hrs will always be the answer in L6 even if you changes their starting time back to normal because the =K6 was overridden with an answer. I would like L6 to have =K6 when i change their start time back to normal.

I hope that helps im not too good at explaining things.

 

[My Aswer Is This]

Try using this script:

If assumes we are dealing with columns D to L
You need no hidden columns and no formulas.
It does look for "OFF"
It looks for values in D to J and puts results in column L

Now see if this works and if it works then we will work on dealing with overtime.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-5-18 4:25 AM EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
If Not Intersect(Target, Range("D6:J" & Lastrow)) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim c As Range
Dim x As Long
x = 0
Dim ans As Long
ans = Target.Row
For Each c In Range(Cells(ans, "D"), Cells(ans, "J"))
If c.Value <> "OFF" And c.Value <> "" Then x = x + 8
Next
Cells(ans, "L").Value = x
End If
End Sub

 

[My Aswer Is This]

I'm also curious.

What do you have in column A B and C
And what do you have in rows 1 to 4

And this image only shows one week of data.
What happens next week?

My script will keep going down as many rows as you want assuming there will always be values in column D
How would we know if overtime was worked any day of the week. And how many hours overtime was worked.

 

[Peter_SSs]

@ delos
Welcome to the MrExcel board!

I have a slightly different interpretation to MAIT but I'm not entirely confident that I have it right. Anyway, if it turns out that MAIT's suggestion is not what you want, you could consider this.

If testing, then do so in a copy of your workbook.

1. Unhide column K and change the formula in K6 to =IF(COUNTA(D6:J6)=7,COUNT(D6:J6)*8,"") then re-hide column K
2. Remove any existing Worksheet_Change code from the sheet's code module and replace it with the code below.
3. Test.

It should allow you to manually enter anything you like in column L.
If anything is changed in columns D:J (below row 5) then
a) If all 7 day columns contain something, the new column K formula will multiply the count of numerical values by 8.
b) If there are any blanks in D:J, the new col K formula will return "" (until all 7 cells contain an entry)
c) In either case a) or b), column L will contain the formula referring to col K again.
d) If multiple cells in columns D:J are changed at once, a) to c) will be repeated for each row where a cell in that range has been changed.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("D:J"), Rows("6:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Cells(c.Row, "L").FormulaR1C1 = "=RC[-1]"
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[My Aswer Is This]

My solution involved needing no formulas in the sheet. If your using a sheet change event I just think you should let the script do all the work and not need formulas in the cells. But then maybe you have your reasons.

 

[delos]

I'm also curious.

What do you have in column A B and C
And what do you have in rows 1 to 4

And this image only shows one week of data.
What happens next week?

My script will keep going down as many rows as you want assuming there will always be values in column D
How would we know if overtime was worked any day of the week. And how many hours overtime was worked.

Thank you for your work but i would prefer a formula hidden in column k just in case i add overtime calculations in the future.

 

[My Aswer Is This]

Thank you for your work but i would prefer a formula hidden in column k just in case i add overtime calculations in the future.

That's great. I assume Peters solution worked for you.

 

[delos]

@ delos
Welcome to the MrExcel board!

I have a slightly different interpretation to MAIT but I'm not entirely confident that I have it right. Anyway, if it turns out that MAIT's suggestion is not what you want, you could consider this.

If testing, then do so in a copy of your workbook.

1. Unhide column K and change the formula in K6 to =IF(COUNTA(D6:J6)=7,COUNT(D6:J6)*8,"") then re-hide column K
2. Remove any existing Worksheet_Change code from the sheet's code module and replace it with the code below.
3. Test.

It should allow you to manually enter anything you like in column L.
If anything is changed in columns D:J (below row 5) then
a) If all 7 day columns contain something, the new column K formula will multiply the count of numerical values by 8.
b) If there are any blanks in D:J, the new col K formula will return "" (until all 7 cells contain an entry)
c) In either case a) or b), column L will contain the formula referring to col K again.
d) If multiple cells in columns D:J are changed at once, a) to c) will be repeated for each row where a cell in that range has been changed.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("D:J"), Rows("6:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Cells(c.Row, "L").FormulaR1C1 = "=RC[-1]"
    Next c
    Application.EnableEvents = True
  End If
End Sub

Thank you formula worked like the way i wanted to and I got to keep my formula. Now i want to do the same for the columns to the right which also has hidden formulas on N6, P6, R6, and T6 and referred to with =N6 on cell O6, =P6 on cell Q6, =R6 on cell S6, and =T6 on cell U6 cells. I will hide the formula cells again but would like to do the same and have the ability to overide cells O6, Q6, S6, and U6. and all the way down again. Thank you again and this should finish what i want to do.