Convert VBA to well written Excel formula or similar?
 

Our business process requires that we support Macs and PCs. In light of the
absence of VBA in Office 2008 for Mac, and business reasons that dictate
that AppleScript is not an option, we are doing analysis to see if a native
well written Excel formula or similar could be used in place of our legacy
VBA below. Essentially it is a two step copy and paste process. My gut
tells me that since we want this to occur on a button click event, we are
out of luck.

Could this be rewritten in an Excel formula or similar without using VBA?
Your creative input would be appreciated.

Thanks,
Mark


Dim s1 As String
Dim s2 As String
Dim y1 As Range
Dim y2 As Range
Dim r As Integer

s1 = "P(" & (ActiveSheet.Range("AD2").Value - 1) & ")"
s2 = "P(" & (ActiveSheet.Range("AD2").Value) & ")"

For r = 1 To 12

' general copy and paste...
Set y1 = Worksheets(s1).Range("B1:W1")
Set y1 = y1.Offset((2 * r) + 4, 0)
y1.Copy
Set y2 = Worksheets(s2).Range("B1:W1")
Set y2 = y2.Offset((2 * r) + 4, 0)
y2.PasteSpecial

' copy the adjusted base salary
Set y2 = Worksheets(s2).Range("P1").Offset((2 * r) + 4, 0)
If Not y2.Value = "" Then
Set y1 = Worksheets(s1).Range("P1").Offset((2 * r) + 4, 0)
y2.Value = y1.Value * (1 + Sheets("Cover").Range("G20").Value)
End If

Next r

For r = 1 To 11

' general copy and paste...
Set y1 = Worksheets(s1).Range("B1:W1")
Set y1 = y1.Offset((2 * r) + 32, 0)
y1.Copy
Set y2 = Worksheets(s2).Range("B1:W1")
Set y2 = y2.Offset((2 * r) + 32, 0)
y2.PasteSpecial

' copy the adjusted base salary
Set y2 = Worksheets(s2).Range("P1").Offset((2 * r) + 32, 0)
If Not y2.Value = "" Then
Set y1 = Worksheets(s1).Range("P1").Offset((2 * r) + 32, 0)
y2.Value = y1.Value * (1 + Sheets("Cover").Range("G20").Value)
End If

Next r