I have a project schedule spreadsheet with this formula structure in many
cells in many columns:

=IF(AND(G15="",G16=""),"",IF(ISERROR(MATCH($E15,$A V$2:$AV$10,0)),WORKDAY(IF(ISBLANK(G16),G15,G16),H$ 14,MGH),WORKDAY(IF(ISBLANK(G16),G15,G16),H$14,INDI RECT($E15))))

It often happens that users need to add or subtract workdays from the
embedded WORKDAY functions--in the example above, they might want to add 10
days to whatever number is in cell H14. (They can always just change the
number in H14, but then all the other cells in the same column that also
refer to H14 will update as well, which users usually don't want to do.)

A few years ago. someone on this site was extremely helpful and provided the
following macro to edit the WORKDAY function in a cell when it is embedded in
long structures, and it has worked extremely well. Here it is:

Sub InputMacro()
Dim strFormula As String
Dim strWkDayFormula1 As String
Dim strWkDayFormula2 As String
Dim strInput As String
Dim lngStart1 As Long
Dim lngEnd1 As Long
Dim lngStart2 As Long
Dim lngEnd2 As Long
Dim rngCell As Range

strInput = InputBox("Please enter the number of days you wish to add or
subtract. Enter added days with a plus sign and subtracted days with a minus
sign.")
If Not IsNumeric(strInput) Then Exit Sub

For Each rngCell In Selection.Cells
Do
lngStart1 = lngStart1 + 1
strFormula = rngCell.Formula
lngStart1 = InStr(lngStart1, strFormula, _
"WorkDay", vbTextCompare)
If lngStart1 0 Then
lngEnd1 = InStr(lngStart1, strFormula, ")", vbTextCompare)
strWkDayFormula1 = Mid(strFormula, lngStart1, _
lngEnd1 - lngStart1 + 1)
lngStart2 = InStr(1, strWkDayFormula1, ",", vbTextCompare)
lngEnd2 = InStr(lngStart2 + 1, strWkDayFormula1, _
",", vbTextCompare)
strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)
rngCell.Formula = Left(strFormula, lngStart1 - 1) & _
strWkDayFormula2 & Right(strFormula, _
Len(strFormula) - lngEnd1)
End If
Loop Until lngStart1 = 0
Next rngCell

End Sub

Unfortunately, the macro doesn't work on the latest version of our schedule
formula, which is what I've cited at the top of this page. The macro seems to
poop out on these lines specifically:

strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)

I suspect the problem is that, in the latest version of the formula, the
first argument of the WORKDAY function has nested IF and IS functions inside
it, so that the macro as written cannot properly identify the end of the
string it is searching. (From what I can make out, the macro seems to look
for strings that begin with "WORKDAY" and are terminated by ")", but since
there are now a number of closing parentheses, it's getting tripped up.)

Can anyone help with tweaking this macro so that it edits the current
formula structure? I'm FAR FAR FAR out of my league with this and don't know
what to do, but I have a lot of users who have relied on this macro for
years, and they will kill me if they have to start hand-editing these
formulas. My own death is unimportant, but some of the users I kind of like,
and I'd like to help them out before I go. Sigh--Can anyone help?

Check the original workbook and see if there is a function IIF that you
forgot to copy to the new workbook

IIf(Left(strInput, 1) = "-", "", "+")

I think this is a recursive function that is missing.

"Steerpike" wrote:

I have a project schedule spreadsheet with this formula structure in many
cells in many columns:

=IF(AND(G15="",G16=""),"",IF(ISERROR(MATCH($E15,$A V$2:$AV$10,0)),WORKDAY(IF(ISBLANK(G16),G15,G16),H$ 14,MGH),WORKDAY(IF(ISBLANK(G16),G15,G16),H$14,INDI RECT($E15))))

It often happens that users need to add or subtract workdays from the
embedded WORKDAY functions--in the example above, they might want to add 10
days to whatever number is in cell H14. (They can always just change the
number in H14, but then all the other cells in the same column that also
refer to H14 will update as well, which users usually don't want to do.)

A few years ago. someone on this site was extremely helpful and provided the
following macro to edit the WORKDAY function in a cell when it is embedded in
long structures, and it has worked extremely well. Here it is:

Sub InputMacro()
Dim strFormula As String
Dim strWkDayFormula1 As String
Dim strWkDayFormula2 As String
Dim strInput As String
Dim lngStart1 As Long
Dim lngEnd1 As Long
Dim lngStart2 As Long
Dim lngEnd2 As Long
Dim rngCell As Range

strInput = InputBox("Please enter the number of days you wish to add or
subtract. Enter added days with a plus sign and subtracted days with a minus
sign.")
If Not IsNumeric(strInput) Then Exit Sub

For Each rngCell In Selection.Cells
Do
lngStart1 = lngStart1 + 1
strFormula = rngCell.Formula
lngStart1 = InStr(lngStart1, strFormula, _
"WorkDay", vbTextCompare)
If lngStart1 0 Then
lngEnd1 = InStr(lngStart1, strFormula, ")", vbTextCompare)
strWkDayFormula1 = Mid(strFormula, lngStart1, _
lngEnd1 - lngStart1 + 1)
lngStart2 = InStr(1, strWkDayFormula1, ",", vbTextCompare)
lngEnd2 = InStr(lngStart2 + 1, strWkDayFormula1, _
",", vbTextCompare)
strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)
rngCell.Formula = Left(strFormula, lngStart1 - 1) & _
strWkDayFormula2 & Right(strFormula, _
Len(strFormula) - lngEnd1)
End If
Loop Until lngStart1 = 0
Next rngCell

End Sub

Unfortunately, the macro doesn't work on the latest version of our schedule
formula, which is what I've cited at the top of this page. The macro seems to
poop out on these lines specifically:

strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)

I suspect the problem is that, in the latest version of the formula, the
first argument of the WORKDAY function has nested IF and IS functions inside
it, so that the macro as written cannot properly identify the end of the
string it is searching. (From what I can make out, the macro seems to look
for strings that begin with "WORKDAY" and are terminated by ")", but since
there are now a number of closing parentheses, it's getting tripped up.)

Can anyone help with tweaking this macro so that it edits the current
formula structure? I'm FAR FAR FAR out of my league with this and don't know
what to do, but I have a lot of users who have relied on this macro for
years, and they will kill me if they have to start hand-editing these
formulas. My own death is unimportant, but some of the users I kind of like,
and I'd like to help them out before I go. Sigh--Can anyone help?

I double-checked the original workbook. The code is exactly as I've pasted it
here, and it works fine on WORKDAY formulas that do not have arguments
replaced with other nested functions. Is your suggestion that now it needs an
additional IIF statement somewhere? I'm afraid these are deep waters for me,
and I'm not sure where it would go.

"Joel" wrote:

Check the original workbook and see if there is a function IIF that you
forgot to copy to the new workbook

IIf(Left(strInput, 1) = "-", "", "+")

I think this is a recursive function that is missing.

"Steerpike" wrote:

I have a project schedule spreadsheet with this formula structure in many
cells in many columns:

=IF(AND(G15="",G16=""),"",IF(ISERROR(MATCH($E15,$A V$2:$AV$10,0)),WORKDAY(IF(ISBLANK(G16),G15,G16),H$ 14,MGH),WORKDAY(IF(ISBLANK(G16),G15,G16),H$14,INDI RECT($E15))))

It often happens that users need to add or subtract workdays from the
embedded WORKDAY functions--in the example above, they might want to add 10
days to whatever number is in cell H14. (They can always just change the
number in H14, but then all the other cells in the same column that also
refer to H14 will update as well, which users usually don't want to do.)

A few years ago. someone on this site was extremely helpful and provided the
following macro to edit the WORKDAY function in a cell when it is embedded in
long structures, and it has worked extremely well. Here it is:

Sub InputMacro()
Dim strFormula As String
Dim strWkDayFormula1 As String
Dim strWkDayFormula2 As String
Dim strInput As String
Dim lngStart1 As Long
Dim lngEnd1 As Long
Dim lngStart2 As Long
Dim lngEnd2 As Long
Dim rngCell As Range

strInput = InputBox("Please enter the number of days you wish to add or
subtract. Enter added days with a plus sign and subtracted days with a minus
sign.")
If Not IsNumeric(strInput) Then Exit Sub

For Each rngCell In Selection.Cells
Do
lngStart1 = lngStart1 + 1
strFormula = rngCell.Formula
lngStart1 = InStr(lngStart1, strFormula, _
"WorkDay", vbTextCompare)
If lngStart1 0 Then
lngEnd1 = InStr(lngStart1, strFormula, ")", vbTextCompare)
strWkDayFormula1 = Mid(strFormula, lngStart1, _
lngEnd1 - lngStart1 + 1)
lngStart2 = InStr(1, strWkDayFormula1, ",", vbTextCompare)
lngEnd2 = InStr(lngStart2 + 1, strWkDayFormula1, _
",", vbTextCompare)
strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)
rngCell.Formula = Left(strFormula, lngStart1 - 1) & _
strWkDayFormula2 & Right(strFormula, _
Len(strFormula) - lngEnd1)
End If
Loop Until lngStart1 = 0
Next rngCell

End Sub

Unfortunately, the macro doesn't work on the latest version of our schedule
formula, which is what I've cited at the top of this page. The macro seems to
poop out on these lines specifically:

strWkDayFormula2 = Left(strWkDayFormula1, lngEnd2 - 1) & _
IIf(Left(strInput, 1) = "-", "", "+") & strInput & _
Right(strWkDayFormula1, Len(strWkDayFormula1) - lngEnd2 + 1)

I suspect the problem is that, in the latest version of the formula, the
first argument of the WORKDAY function has nested IF and IS functions inside
it, so that the macro as written cannot properly identify the end of the
string it is searching. (From what I can make out, the macro seems to look
for strings that begin with "WORKDAY" and are terminated by ")", but since
there are now a number of closing parentheses, it's getting tripped up.)

Can anyone help with tweaking this macro so that it edits the current
formula structure? I'm FAR FAR FAR out of my league with this and don't know
what to do, but I have a lot of users who have relied on this macro for
years, and they will kill me if they have to start hand-editing these
formulas. My own death is unimportant, but some of the users I kind of like,
and I'd like to help them out before I go. Sigh--Can anyone help?