Help with Macro Using InStrRev: How to Rewrite to Use on Macs and
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?
|