Help with MACRO to Edit WORKDAY Formula?

[Wart]

A couple of years ago, someone here helped me with a thorny problem. The
following formula is used in a production schedule to calculate workdays
under varying conditions:

=IF(ACT_Dates<3,IF(AN21<AM21,(IF(AM21=0,0,WORKDAY(AM21,AO$20,$EC$2:$EC$16))),(IF(AN21=0,0,WORKDAY(AN21,AO$20,$EC$2:$EC$16)))),IF(AN21=0,(IF(AM21=0,0,WORKDAY(AM21,AO$20,$EC$2:$EC$16))),WORKDAY(AN21,AO$20,$EC$2:$EC$16)))

I don't think a complete annotation is necessary--really, the important part
is just the embedded WORKDAY function:

WORKDAY(AM21,AO$20,$EC$2:$EC$16)

The thorny problem was this: That my users need to be able to change the
number of days used in the calculation (A0$20, in this example) without
actually changing the source cell's number--variations on this formula appear
bazillions of time in the spreadsheet, the users have to be able to edit a
single cell without changing the number of days being picked up by all the
other cells.

They could just manually edit, but to avoid that (and to minimize editing
errors), the wonderful person I menioned above provided the following macro:

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

The macro asks the user for how many days they want to add or subtract from
the formula in the active cell and then edits the formula accordingly. It
works great.

Over the years, though, the scheduling spreadsheet has gotten more
complicated, and now the standard formula looks like this:

=IFISERROR(IF(ACT_Dates<3,IF(X35<V35,(IF(V35=0,0,WORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B$21:$Y$21,0),FALSE),$D$2:$D$16))),(IF(V35=0,0,WORKDAY(X35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B$21:$Y$21,0),FALSE),$D$2:$D$16)))),IF(X35=0,(IF(V35=0,0,WORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B$21:$Y$21,0),FALSE),$D$2:$D$16))),WORKDAY(X35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B$21:$Y$21,0),FALSE),$D$2:$D$16))),)

Again, probably a full annotation isn't necessary. What I really need help
with is: How do I adapt the macro to work with the new structure? The second
argument of that function has been replaced with:

VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B$21:$Y$21,0),FALSE)

--and of course the macro doesn't know what to do. The problem is
intensified because, in some cells (owing to different IF clauses), the whole
workday function has two parentheses at the end, and in others it has
three--and the macro needs to be able to edit both. "FALSE)" always appears
as in my example, though, so maybe that would be a constant landmark?

Anyway: Sorry about the long posting, but I'm desparate. Can anyone help?

 

[Bob Phillips]

This should handle both types, it works by matching off internal brackets

Sub InputMacro()
Dim sFormula As String
Dim sWDFormula1 As String
Dim sWDFormula2 As String
Dim sInput As String
Dim iStart1 As Long
Dim iEnd1 As Long
Dim iStart2 As Long
Dim iEnd2 As Long
Dim rngCell As Range
Dim cPairs As Long

sInput = InputBox("Please enter the number of days to add or subtract."
& vbNewLine & _
"Enter added days without a sign, subtracted days
with a leading -.")
If Not IsNumeric(sInput) Then Exit Sub

For Each rngCell In Selection.Cells

Do

iStart1 = iStart1 + 1
sFormula = rngCell.Formula
iStart1 = InStr(iStart1, sFormula, "WorkDay(", vbTextCompare)
If iStart1 > 0 Then

icPairs = 0
iStart1 = iStart1
iEnd1 = iStart1 + 6
Do

iEnd1 = iEnd1 + 1
If Mid$(sFormula, iEnd1, 1) = "(" Then

cPairs = cPairs + 1

ElseIf Mid$(sFormula, iEnd1, 1) = ")" Then

cPairs = cPairs - 1
End If

Loop Until cPairs = 0

sWDFormula1 = Mid(sFormula, iStart1, iEnd1 - iStart1 + 1)
iStart2 = InStr(1, sWDFormula1, ",", vbTextCompare)
iEnd2 = InStrRev(sWDFormula1, ",", , vbTextCompare)
sWDFormula2 = Left(sWDFormula1, iEnd2 - 1) & _
IIf(Left(sInput, 1) = "-", "", "+") & _
sInput & _
Right(sWDFormula1, Len(sWDFormula1) -
iEnd2 + 1)
rngCell.Formula = Left(sFormula, iStart1 - 1) & _
sWDFormula2 & _
Right(sFormula, Len(sFormula) -
iEnd1)
End If
Loop Until iStart1 = 0
Next rngCell

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Wart]

EXCELLENT! It works PERFECTLY! Thank you so much--you've just made my day a
little bit (no, a LOT) better. I would never have figured this out. (Hell, I
was an English major, after all.)

Thanks again!