ExcelBanter - View Single Post

John Coleman

wrote:
Is a miserable failure.

Hi all -

Just trying to subtract E from D rows ever expanding and contracting.

"Compile error: Expected: end of statement"
When I enter after typing this bit of code:

With frmRng
.FormulaR1C1 = RC[-2] - RC[-1] 'This line appears red

Full code below
Thanks much
-goss

''''''''''''''''''''''''''''''''''''''''
Sub wa_splh_analysis()

Dim wbBook As Workbook
Dim wsPayrollData As Worksheet
Dim wsLaborAnalysis As Worksheet
Dim lngRows As Long
Dim copyRng As Range
Dim destRng As Range
Dim frmRng As Range
Dim C As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set wbBook = ThisWorkbook

With wbBook
Set wsPayrollData = .Worksheets("Payroll_Data")
Set wsLaborAnalysis = .Worksheets("LaborAnalysis")
End With

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''
'Get Hours

With wsPayrollData
lngRows = Range("A65536").End(xlUp).Row
Set copyRng = .Range("M3:N" & lngRows)
End With

With wsLaborAnalysis
Set destRng = .Range("D3")
End With

copyRng.Copy
With destRng
.PasteSpecial xlPasteValuesAndNumberFormats
.PasteSpecial xlPasteFormats
End With

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''

'Formula
With wsLaborAnalysis
Set frmRng = .Range("F4:F" & lngRows)
End With

With frmRng
.FormulaR1C1 = RC[-2] - RC[-1]

End Sub

Formulas are strings. Try

..FormulaR1C1 = "RC[-2] - RC[-1]"

don't forget end with