Help with Macro Using InStrRev: How to Rewrite to Use on Macs and

[Wart]

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)

 

[Bob Phillips]

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]

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]

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)