Give this a try. You should be able to select the cells you want changed,
and run the macro. It will pop up an inputbox, negative numbers s/b input as
such (ie -7), positive numbers just input as a number and it will put in the
+ sign. It will add/subtract whatever you input from the second parameter of
the workday functions in the formula.

The NG will probably wrap/break some of the lines in the wrong places, so
you'll need to correct when you paste into a module.


Sub test()
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("Input Number")
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


"Michael Link" wrote:

Unfortunately, I'm not sure if that's practical. The spreadsheet is very
large, and some version of this same formula appears in many cells, so I'd be
increasing the size of the sheet beyond practicality with all the input cells
I'd require.

I truly think I'm just screwed.

Thanks for the suggestion, though!

"JMB" wrote:

Can you set up a separate cell for the adjusment, and just reference that
cell in your formulae?

"Michael Link" wrote:

I have a large spreadsheet with many iterations of the same basic formula:

=IF($F18="Y",IF(V18<U18,IF(U18=0,0,WORKDAY(U18,X$1 7,$Z$5:$AB$13)),IF(V18=0,0,WORKDAY(V18,X$17,$Z$5:$ AB$13))),IF(V18<U18,IF(U18=0,0,WORKDAY(U18,X$17,$Z $5:$Z$13)),IF(V18=0,0,WORKDAY(V18,X$17,$Z$5:$Z$13) )))

Users often need to edit the embedded WORKDAY functions to add or subtract
days from the value referenced in X17. In the example above, a user would
need add "+7" after the "X17" to add 7 days, and they need to do it four
times since the WORKDAY function appears four times. (They can't just change
the value in X17, because other cells feed off of X17 which do not require
adjustment.)

Is it possible to write a macro to simplify this routine, so that, when run,
a pop-up box would ask the user for the number of days they want to add or
subtract from the WORKDAY function in the active cell's formula, no matter
what that larger formula's syntax was? (Essentailly, the macro would need to
identify and alter ONLY the WORKDAY function in the active cell.) Or is this
just one of those impossible things?

Thanks for any guidance anyone can offer!

Depressed in Excelworld