my first attempt at R1C1 in vba

[goss9394]

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

 

[John Coleman]

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

 

[Tushar Mehta]

Use the macro recorder to learn the correct syntax. First, switch the
GUI to R1C1 mode (Tools | Options... | General tab | check 'R1C1
reference style'. Next, turn on the recorder (Tools | Macro > Record
new macro...). Now, enter the formula of interest in some cell (use
the mouse to click cells of interest). Finally, turn off the macro
recorder and switch to the VBE. XL will give you the necessary code.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[John Coleman]

Should have been

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

I think

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

 

[goss9394]

Thanks John
This worked
..FormulaR1C1 = "=RC[-2] - RC[-1]"

Thanks for your help
-goss

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