If cells b5, d5, and/or g5 are empty (one or all), run a formula to calculate a due date

[eabaker64]

Hi. I need a formula in B3 to look at cells B5, D5 and G5. If any of those cells are blank, a formula is run which calculates a date that is 15 workdays after a date showing in cell B2. If cells B5, D5 and G5 all contain data, the formula does not run and instead returns a value of N/A in cell B3. I have this formula but if any of the B5, D5, or G5 cells have data, it returns N/A. I don't want it to return N/A unless all three cells contain data. Any ideas?

=IF(B5<>"","N/A",IF(D5<>"","N/A",IF(G5<>"","N/A",IF(B2<>"",WORKDAY(B2,15),""))))

 

[eabaker64]

Okay. Maybe I am doing something wrong. I have taken the formulas out of one of the worksheets, but left it in one of the other ones, just to see if it made a difference or not. I entered the code into the module for each worksheet. I should just be able to enter information on the worksheet in B5 (which is 15 working days from when the original report/attachment was sent, which will then calculate the date for B6) and have it do the calculations for the due dates, and check whether data is in B9 & D9, correct?

 

[dreid1011]

Okay. Maybe I am doing something wrong. I have taken the formulas out of one of the worksheets, but left it in one of the other ones, just to see if it made a difference or not. I entered the code into the module for each worksheet. I should just be able to enter information on the worksheet in B5 (which is 15 working days from when the original report/attachment was sent, which will then calculate the date for B6) and have it do the calculations for the due dates, and check whether data is in B9 & D9, correct?

I didn't add B5 into the calculations. Essentially, it will run when dates are entered into the following cells:

B8, B9, D6 and D9

If something is needed to run when B5 is populated, that can be added.

 

[eabaker64]

I didn't add B5 into the calculations. Essentially, it will run when dates are entered into the following cells:

B8, B9, D6 and D9

If something is needed to run when B5 is populated, that can be added.

I entered a date into B8 but it did not populate anything else. That was on both worksheets...the one with the formulas removed and the other with the formulas remaining in the cells. It must be something I am doing wrong, or not doing that should be done. ugh. Sorry.

 

[dreid1011]

Can you take a screenshot of your VBA editor like this:

 

[eabaker64]

Can you take a screenshot of your VBA editor like this:

View attachment 114163

 

[eabaker64]

Can you take a screenshot of your VBA editor like this:

View attachment 114163

Let me try that again.

 

[eabaker64]

Let me try that again.

 

[dreid1011]

Does it do anything when you enter dates into any of the cells I mentioned?

 

[eabaker64]

Does it do anything when you enter dates into any of the cells I mentioned?

I have entered dates in all four cells, both on the worksheet that had formulas in other cells, and in the worksheet with no formulas, and neither one did anything.

 

[dreid1011]

Okay, first I added a line to the code, so try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
        
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub

And second, in the Immediate window, type "Application.EnableEvents = True" (without quotes) and press enter.

If your Immediate window is not open, either press Ctrl + G or go to View, and select Immediate Window.

After entering the line above, try to enter dates into the cells.