Instead of continuing the original post ("Help with Macro to Edit Formula?"),
I'm starting over here to make it stand out more as a separate query. Bob
Phillips helpfully provided the following code in response to my original
query. (Both his code and my query are below.) Unfortunately, I've just
discovered that the code works great on a PC--but not on a Mac, which is what
some of our company's departments use. I think the code is getting tripped
up on the InStrRev function, which seems not to be avaialbe in VBA for Mac
2004.

Is there a fix anyone can see that would make the macro compatible with both
platforms?

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" wrote in message
...
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(AN 21,AO$20,$EC$2:$EC$16)))),IF(AN21=0,(IF(AM21=0,0,W ORKDAY(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,W ORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B $21:$Y$21,0),FALSE),$D$2:$D$16))),(IF(V35=0,0,WORK DAY(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$2 1,$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?

If the Mac really doesn't have InStrRev, you can always craft you own and
apend it to your code

Private Function InStrRev(stringcheck, stringmatch, Optional start, Optional
compare)
Dim i As Long
For i = Len(stringcheck) To 1 Step -1
If Mid$(stringcheck, i, 1) = stringmatch Then Exit For
Next i
InStrRev = i
End Function


--
---
HTH

Bob


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



"Wart" wrote in message
...
Instead of continuing the original post ("Help with Macro to Edit
Formula?"),
I'm starting over here to make it stand out more as a separate query. Bob
Phillips helpfully provided the following code in response to my original
query. (Both his code and my query are below.) Unfortunately, I've just
discovered that the code works great on a PC--but not on a Mac, which is
what
some of our company's departments use. I think the code is getting
tripped
up on the InStrRev function, which seems not to be avaialbe in VBA for Mac
2004.

Is there a fix anyone can see that would make the macro compatible with
both
platforms?

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" wrote in message
...
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(AN 21,AO$20,$EC$2:$EC$16)))),IF(AN21=0,(IF(AM21=0,0,W ORKDAY(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,W ORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B $21:$Y$21,0),FALSE),$D$2:$D$16))),(IF(V35=0,0,WORK DAY(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$2 1,$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?

Thanks, Bob! I appended the code you provided and now the macro works
perfectly on both my PC at home and the Macs at work. (It would be nice if
the PC and Mac versions of XL were fully compatible with each other, but I
guess that's right up there with world peace and a sugar substitute that
tastes like sugar.) I truly appreciate all of the assistance you've given me,
and I know the users of the spreadsheet will, too.

Thanks again!

"Bob Phillips" wrote:

If the Mac really doesn't have InStrRev, you can always craft you own and
apend it to your code

Private Function InStrRev(stringcheck, stringmatch, Optional start, Optional
compare)
Dim i As Long
For i = Len(stringcheck) To 1 Step -1
If Mid$(stringcheck, i, 1) = stringmatch Then Exit For
Next i
InStrRev = i
End Function


--
---
HTH

Bob


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



"Wart" wrote in message
...
Instead of continuing the original post ("Help with Macro to Edit
Formula?"),
I'm starting over here to make it stand out more as a separate query. Bob
Phillips helpfully provided the following code in response to my original
query. (Both his code and my query are below.) Unfortunately, I've just
discovered that the code works great on a PC--but not on a Mac, which is
what
some of our company's departments use. I think the code is getting
tripped
up on the InStrRev function, which seems not to be avaialbe in VBA for Mac
2004.

Is there a fix anyone can see that would make the macro compatible with
both
platforms?

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" wrote in message
...
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(AN 21,AO$20,$EC$2:$EC$16)))),IF(AN21=0,(IF(AM21=0,0,W ORKDAY(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,W ORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B $21:$Y$21,0),FALSE),$D$2:$D$16))),(IF(V35=0,0,WORK DAY(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$2 1,$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?

It's even getting worse because as I understand it, Apple have dropped VBA
in 2008 for AppleScript.

--
---
HTH

Bob


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



"Wart" wrote in message
...
Thanks, Bob! I appended the code you provided and now the macro works
perfectly on both my PC at home and the Macs at work. (It would be nice if
the PC and Mac versions of XL were fully compatible with each other, but I
guess that's right up there with world peace and a sugar substitute that
tastes like sugar.) I truly appreciate all of the assistance you've given
me,
and I know the users of the spreadsheet will, too.

Thanks again!

"Bob Phillips" wrote:

If the Mac really doesn't have InStrRev, you can always craft you own and
apend it to your code

Private Function InStrRev(stringcheck, stringmatch, Optional start,
Optional
compare)
Dim i As Long
For i = Len(stringcheck) To 1 Step -1
If Mid$(stringcheck, i, 1) = stringmatch Then Exit For
Next i
InStrRev = i
End Function


--
---
HTH

Bob


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



"Wart" wrote in message
...
Instead of continuing the original post ("Help with Macro to Edit
Formula?"),
I'm starting over here to make it stand out more as a separate query.
Bob
Phillips helpfully provided the following code in response to my
original
query. (Both his code and my query are below.) Unfortunately, I've just
discovered that the code works great on a PC--but not on a Mac, which
is
what
some of our company's departments use. I think the code is getting
tripped
up on the InStrRev function, which seems not to be avaialbe in VBA for
Mac
2004.

Is there a fix anyone can see that would make the macro compatible with
both
platforms?

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" wrote in message
...
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(AN 21,AO$20,$EC$2:$EC$16)))),IF(AN21=0,(IF(AM21=0,0,W ORKDAY(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,W ORKDAY(V35,VLOOKUP(B35,FIRST_2_TURN,MATCH($Y$21,$B $21:$Y$21,0),FALSE),$D$2:$D$16))),(IF(V35=0,0,WORK DAY(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$2 1,$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?