CODEWITHSUNDEEP
excelvba
Pourya Ashena
Pourya Ashena

Reputation: 49

Adjust ActiveCell.FormulaR1C1

Hi I have a question I have code that i made with macro recorder:

ActiveCell.FormulaR1C1 = "=New_Order!RC[-42]+New_Order!RC[-41]+New_Order!RC[-40]"

how can I adjust this code so it would be somthing like thi:

'ActiveCell.Formula = Sheets("New_Order").Range(n & LastRow) + Sheets("New_Order").Range(O & LastRow) + Sheets("New_Order").Range(p & LastRow)

I have values in a different sheet called New_Order in column N, O, P. i want to sum these values and auto fill entire column in worksheet Registration. the complete code looks like this:

 Sub Registrereren()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

On Error Resume Next

Dim oWkSht As Worksheet
Dim LastColumn As Long
Dim c As Date
Dim myCell As Range
Dim LastRow As Long

Sheets("Registration").Activate


Set oWkSht = ThisWorkbook.Sheets("Registration")
LastColumn = oWkSht.Range("A" & Columns.Count).End(xlToRight).Column
LastRow = oWkSht.Range("C" & Rows.Count).End(xlUp).Row

c = Date

Set myCell = oWkSht.Range("1:1").Find(What:=c, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns)

If Not myCell Is Nothing Then
    myCell.Offset(1, 0).Select

    ActiveCell.FormulaR1C1 = "=New_Order!RC[-42]+New_Order!RC[-41]+New_Order!RC[-40]"
    'ActiveCell.Formula = Sheets("New_Order").Range(n & LastRow) + Sheets("New_Order").Range(O & LastRow) + Sheets("New_Order").Range(p & LastRow)
    myCell.Offset(1, 0).Select
    Range(myCell.Offset(1), Cells(LastRow, myCell.Column)).Select
    Selection.FillDown

End If

Sheets("Main").Activate

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Upvotes: 2

Views: 1028

Answers (2)

EEM
EEM

Reputation: 6659

Assuming the formula you want to enter is:

= 'New_Order'!NR + 'New_Order'!OR + 'New_Order'!PR

where R is the same row of the cell holding the formula, then use this FormulaR1C1:

= 'New_Order'!RC14 + 'New_Order'!RC15 + 'New_Order'!RC16

Replace this line:

ActiveCell.FormulaR1C1 = "=New_Order!RC[-42]+New_Order!RC[-41]+New_Order!RC[-40]"

with this line:

ActiveCell.FormulaR1C1 = "=New_Order'!RC14 + 'New_Order'!RC15 + 'New_Order'!RC16"

Upvotes: 1

Amorpheuses
Amorpheuses

Reputation: 1423

I've made a number of assumptions and, as a result, a number of corrections. I assume that you have entries in column "A" of the Registration sheet that match the number of rows you have in the New_Order sheet. 

Option Explicit


Sub Registrereren()

  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  Application.ScreenUpdating = False

  On Error Resume Next

  Dim oWkSht As Worksheet
  Dim LastColumn As Long
  Dim c As Date
  Dim myCell As Range
  Dim LastRow As Long

  Sheets("Registration").Activate

  Set oWkSht = ThisWorkbook.Sheets("Registration")
  LastColumn = oWkSht.Cells(1, oWkSht.Columns.Count).End(xlToLeft).Column
  LastRow = oWkSht.Cells(oWkSht.Rows.Count, "A").End(xlUp).row

  c = Date
  Set myCell = oWkSht.Cells(1, 1).Resize(, LastColumn).Find(What:=c, LookIn:=xlFormulas, _ 
               lookat:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns)

  If Not myCell Is Nothing Then
    myCell.Offset(1, 0).Formula = "=New_Order!N1+New_Order!O1+New_Order!P1"
    Range(myCell.Offset(1), Cells(LastRow, myCell.Column)).Select
    Selection.FillDown
  End If

  Sheets("Main").Activate

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions