W
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?
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?