+ Reply to Thread
Results 1 to 5 of 5

my first attempt at R1C1 in vba

  1. #1

    my first attempt at R1C1 in vba

    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


  2. #2
    John Coleman
    Guest

    Re: my first attempt at R1C1 in vba


    [email protected] 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


  3. #3
    Tushar Mehta
    Guest

    Re: my first attempt at R1C1 in vba

    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

    In article <[email protected]>,
    [email protected] says...
    > 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
    >
    >


  4. #4
    John Coleman
    Guest

    Re: my first attempt at R1C1 in vba

    Should have been

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

    I think

    John Coleman wrote:
    > [email protected] 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



  5. #5

    Re: my first attempt at R1C1 in vba

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

    Thanks for your help
    -goss


    John Coleman wrote:
    > [email protected] 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1