my first attempt at R1C1 in vba

G

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
 
J

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
Click to expand...

Formulas are strings. Try

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

don't forget end with
 
T

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
 
J

John Coleman

Should have been

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

I think

John said:
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
Click to expand...

Formulas are strings. Try

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

don't forget end with
Click to expand...
 
G

goss9394

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

Thanks for your help
-goss


John said:
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
Click to expand...

Formulas are strings. Try

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

don't forget end with
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code logic error 1
Error on pastespecial 2
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Create new book..paste data..save..close 1
Search routine needs a little work 2
Macro Problem 4
Formatting and copy or copy and formatting ?? 2
My Macro is slower than the turtle 1

Top