Free Microsoft Excel 2013 Quick Reference

Using FormulaR1C1

I am looping through a set of data to find two specific points. I save the row# as integer variables x and z. I then want to calculate the standard deviation of a column if values begining with row x and ending with row z. I tried using the FormulaR1C1 as:

Range("J4").Select
ActiveCell.FormulaR1C1 = "=STDEV(R[x]C[36]:R[z]C[36])"

Where I am storing the result from the calculation in cell J4.

What am I doing wrong?

Thanks


I am trying to set up a formula for several cells and am having a problem
because extra single quotes are appearing.

I use the following code:

Set wc = Worksheets("Cost")

startcol = 14
n = 0
wdarow = 24
For m = 1 To 80 Step 4
n = n + 1
wc.Range(Cells(8, startcol + m), Cells(8, startcol + m)).FormulaR1C1
= "=CB.GetForePercentFN('Timing Analysis'!AE" & wdarow & ",pValue)"
wdarow = wdarow + 7
If n = 2 Then
wdarow = wdarow + 9
n = 0
End If
Next m

and the result in the cell is:

=CB.GetForePercentFN('Timing Analysis'!'AE31',pValue)

If I remove the single quotes around AE31 it works fine but what I don't
understand is why these quotes are appearing in the first place.

Thanks

Neme

Given a worksheet function that accepts a range of values, Average(Range),
for example.....

What is the method to use the .formulaR1C1 method within VBA to assign the
Average formula to a cell, assuming that I want to define the formula from
within VBA using the Range/Cells method(s).

Example:

Macro recorder provides the following code to define a cell using the
Average function:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"

Can this be done via something like:

Dim Rindex as Integer
Dim Cindex as Integer

Rindex=3
Cindex=7

strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
ActiveCell.FormulaR1C1 = "=" & strArg

and have it parse correctly....

i.e. I would like to be able to manipulate the .FormulaR1C1 line via
variables that get worked upon prior to setting the formula in the cell.

Thank you in advance for the assistance.

--

Frank Bachman
(Grumpy Aero Guy)

I have a line of code that sets the values in a range of cells = to the value of a corresponding cell * the value in another cell whose column reference is absolute (i.e the values in range K7:AF7 = the values in K39:AF39 * the value in I39. I would insert a lot more code here, but it would probably be a waste.


	VB:
	
 caculate_values () 
    titleRowNum = Range("I1").Value 
    analyteNum = Range("F1").Value 
    ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Select 
    Selection.FormulaR1C1 = "=R[3]C*R[3]C9" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So here is what I need to do. Consider that I am trying to copy the values in cells K39:AF39 into cells K7:AF7, where they are multiplied by the value in I39. I have some cells in the ranges K39:AF39 that are null, and when I copy those values up into the Range K7:AF7, and they are multiplied by the value in I39, they become 0. I need them to remain null.

So I need to do an "IF the value in K7 is not null, then execute the code above, else make the value in K7 null"...the problem is I'm having a difficult time understanding the Formula R1C1 syntax.

The ENTIRE sub is shown below:

	VB:
	
 HRC3_caL2_amount_snippet() 
     
    Dim myStr, anyStr 
    Dim RSDNum, titleColNum, titleRowNum, convertConst, myCase As Integer 
    Dim countPblk, smpNoCol, analyteNum, smpNum As Integer 
     
    Call smpType 
     
    myCase = Range("B1").Value 
    countPblk = Range("C1").Value 
    smpNoCol = Range("D1").Value 
    analyteNum = Range("F1").Value 
    smpNum = Range("G1").Value 
    RSDNum = Range("H1").Value 
    titleRowNum = Range("I1").Value 
    titleColNum = Range("J1").Value 
     
    Range("A1").Select 
     
    Cells(titleRowNum + 1, 1).Select 
    ActiveCell.EntireRow.Insert Shift:=xlDown 
    ActiveCell.Value = "Measured Concentration in Solution" 
    ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Value = MeasConc 
    ActiveCell.EntireRow.Select 
    Selection.Insert Shift:=xlDown 
    ActiveCell.Range(Cells(1, 1), Cells(1, (titleColNum + analyteNum + RSDNum + 1))).Select 
    Selection.RowHeight = 6 
    With Selection.Interior 
        .ColorIndex = 40 
        .Pattern = xlSolid 
    End With 
     
    Cells(titleRowNum + 1, 1).Select 
    ActiveCell.EntireRow.Select 
    Selection.Insert Shift:=xlDown 
     
    ActiveCell.Range(Cells(1, 1), Cells(1, titleColNum)).FormulaR1C1 = "=R[3]C" 
     
    ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Select 
    Selection.FormulaR1C1 = "=R[3]C*R[3]C9" 'amount unit in "ng" but in "ug" for bricks
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


the code i used to find the second instance in sheet2 which corresponds to a value in sheet1.

If there isent a second instance i want "" to appear.


	VB:
	
 
Dim last_row As Long 
 
last_row = Range("H65536").End(xlUp).Row 
 
For c = last_row To 2 Step -1 
     
    If ActiveSheet.Cells(c, 8).Value = "" Then 
         
        ActiveSheet.Cells(c, 11).FormulaR1C1 =
"=INDEX(Sheet2!R2C2:R1063C2,SMALL(IF(Sheet2!R2C1:R1063C1=main!RC[-11],ROW(Sheet2!R2C2:R1063C2)-ROW(Sheet2!R2C2)+1,""""),2))" 
    Else 
         
        ActiveSheet.Cells(c + 1, 11).EntireRow.Insert 
        ActiveSheet.Cells(c + 1, 11).FormulaR1C1 =
"=INDEX(Sheet2!R2C2:R1063C2,SMALL(IF(Sheet2!R2C1:R1063C1=main!RC[-11],ROW(Sheet2!R2C2:R1063C2)-ROW(Sheet2!R2C2)+1,""""),2))" 
         
    End If 
     
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When i run this code i get a #VALUE! error


	VB:
	
Range("L4").FormulaArray =
"=INDEX(Sheet2!$B$2:$B$1063,SMALL(IF(Sheet2!$A$2:$A$1063=main!A4,ROW(Sheet2!$B$2:$B$1063)-ROW(Sheet2!$B$2)+1,""""),2))" 
Range("L4:L464").FillDown 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But when i run this code, it works perfectly.

Can u use FormulaR1C1 with a FormulaArray?

I am very new to vba and I was wondering if someone can clarify for me how the R1C1 function works with respects to defined variables.
Suppose I have defined cell K3 as a variable and suppose this cell currently has a value of 10. When I code the variable in the FormulaR1C1, it takes the hardcoded number 10 instead of the cell reference K3. I want to create a vba code such that if K3 were to change, the formula would be referencing Cell K3 and not hardcoded number in K3.

Apologies if my question is unclear. Thanks!!


	VB:
	
 Single 
variable = Range("K3") 
ActiveCell.FormulaR1C1 = "=product(R[0]C[-2]," & variable & ")" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all

I have a sheet where i reference to data on other sheets, every time i add a new sheet to the workbook i name the sheet and add
a new row to the main sheet and reference the data in the row via FormulaR1C1, this work fine as long as I use a text variable to reference the sheet name.

The sheet names are all to numbers separated with a dot, for example(1.1, 1.2, 2.12, 22.11, ….)
My regional settings are Danish, when I change the separator to a comma I can sort my list as text, but my code slams to a hold.

If I manually writes the code in each cell I can use the sheet names with the comma separator, but when I use FormulaR1C1, I get nowhere.

Here is the code I use to fill the cells at the main sheet:


	VB:
	
 qws 
    .Cells(StopRow, 1).value = vPos 
    .Cells(StopRow, 2).FormulaR1C1 = "=" & vPos & "!R1C2" ‘ here the code stops 
    .Cells(StopRow, 3).FormulaR1C1 = "=" & vPos & "!R1C1" 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I set vPos As string with dot separator no problem, but I change it to a comma separator it fails.

Please anyone with a hint ?

Hello again,
I need to create formulas based on the contents of the above cell.
An example:
The dates, however, are not going to be static.  The date range must always be for eight years, but it will be eight years
back from the previous month end.  
I think I can figure out how to change dates around so that it's always 8 years back from the current date, but I don't know how to handle inputting variables into that formula. There are too many quotation marks.
Anyway, I'm kind of confused, any help appreciated, thanks.

Good Day

I'm writing a Macro that must find all fields that start with a space and move them to another column depending on the number of spaces.

Eg.from this
Carrots           6
Onions            7
  Veggies       13
Bananas          5
Oranges          9
Apples            2
   Fruits         16
     Fridge      29
To this:
Carrots           6         Veggies       13    Fridge      29
Onions            7         Veggies       13    Fridge      29
Bananas          5         Fruits         16    Fridge      29
Oranges          9         Fruits         16     Fridge      29
Apples            2         Fruits         16     Fridge      29
I'm thinking of something like this in terms of code:

  Sheets("Sheet1").Select

' Start moving fields
   ActiveWorkbook.Names.Add Name:="Start", RefersTo:=Range("A6")
   Range("Start").Select

    Do
        If ActiveCell.FormulaR1C1 = "     " & (Whatever is contained in the field) Then
            Range(ActiveCell, ActiveCell.End(xlToRight)).Select
            Selection.Cut 
        End If
        
        
        ActiveCell.Offset(1, 0).Activate

    Loop Until ActiveCell.Value = ""
Thanks for your help:

Hi,

I want to create a macro in a workbook that automatically links to
other worksheets. For instance, if i run the following code below, cell
E12 will automatically link to cell E12 in sheet "Accounting Data" of
file "6460.xls" in folder "C:My DocumentsAccounts

Range("E12").FormulaR1C1 = "='C:My
DocumentsAccounts[6460.xls]Accounting Data'!RC"

I could repeat this code over and over again in reference to other cell
ranges, but I'd like to clean it up. I've attempted to do so, with the
following code:

'------------------------------
Sub GetDataFromFolder()
Dim myFolder As String
Dim myFile As String
Dim mySheet As String

'Range("E12").FormulaR1C1 = "='C:My
DocumentsAccounts[6460.xls]Accounting Data'!RC"
myFolder = "C:My DocumentsAccounts"
myFile = "[6460.xls]"
mySheet = "Accounting Data'!RC"

Range("E12").FormulaR1C1 = "=&myFolder & myFile & mySheet"

End Sub
'-----------------------------

BUT - i get the following error

Run-time Error 1004
Application - Defined or Object Error

can some let me know how to fix this? I'm not sure if i need to use
"formulaR1C1" references or not. Thanks in advance

Hello,

We use an engineering application that creates reports of pipeline data in excel. We then have to use part of this data to complete the list using some calculations and by looking up standard tables in another excel file or sheet. I have been given the task of improving the macros.

1. Most of the calculations have been done using absolute ForumlaR1C1, and by referencing other columns with offsets. Trouble is, when the column arrangement of the exported data is changed, the code has to be changed too. How could I avoid this? Would it be better to use find?

2. Is it faster to use FormulaR1C1 rather than calculating or doing a VLOOKUP in the VBA code itself and just filling the table with resulting data?


	VB:
	
ActiveSheet.Cells(Fila, 19).FormulaR1C1 = "=RC[2]*1.5" 
ActiveSheet.Cells(Fila, 20).FormulaR1C1 = "=VLOOKUP(RC[-13],'Equivalencias diámetros'!R2C1:R31C2,2,FALSE)" 
ActiveSheet.Cells(Fila, 21).FormulaR1C1 = "=RC[-4]" 
ActiveSheet.Cells(Fila, 22).FormulaR1C1 = "=RC[-1]*RC[-2]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I attach a portion of the data and some of the code, hope that helps clarify my question.
Thanks in advance!
Sang

hi there and mery chrismas

how can i write a macro to add sum formula in certen row to make sum for certen number of rows (these rows are variable each time )without mention the name or number of cell

example
if i have value in cell a1 & a2 & a3 & a4
i need to have macro to add sum(a1:a4) in cell a5 without mention a1 & a4
in the macro because i need to use it any where in sheet not sut for column a
i try to use formular1c1

first_row = selection.row
( the row no where i want to start sum )
application.formular1c1=" =sum(r[-first_row]c:r[-1]c"
but it give me error

thank u in adv. for help

I want to put a formula that will add the column but I don't want to anchor the first reference in the sum range.


	VB:
	
Range("H" & lastrow + 1).Formula = "=SUM(R2C:R[-1]C)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
lastrow is the lastrow in the range. After running the macro the formula gives me SUM(H$2:H235) which provides me the correct amount but I need to unachor the H$2. Does anyone know if there is a way to unanchor?

Using .FormulaR1C1, I want to use a a formula with a varriable to define one of the relative row offsets. Lines is a variable, in this case Lines= 7. The R1C1 where I'm using the formula should come up bold below.


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=IF(INDEX(R[-1]C[-13]:R[-1]C[-3],1,MATCH( " & LowTotal.Address(ReferenceStyle:=xlR1C1) & " ," &
Totals.Address(ReferenceStyle:=xlR1C1) & ",0))

In Sheet1,

C14 = Sheet2!D2
C15 = Sheet2!D3
C16 = Sheet2!D5

What i want to do is to change "D" to "E". Next time i run it, it will change from "E" to "G".

I want a macro to do it, so that I don't have to change it manually one by one. For some reason, when i try to use .formulaR1C1, the reference change, and I couldn't get it to work.

someone please help. Thanks in advance.

Good afternoon,
I have put together the following code to copy a manually entered spreadsheet, and then change the values of a range of cells with a formula.

The first sheet is a log to track invoice installments. Column M in the first sheet lists a discount rate. The discounted billing amount is entered in the appropriate cells in columns P through the last used column for each row.

This macro is to create a duplicate for reporting to another department (I believe to track commissions) but needs to report the amounts in Columns P through the last used columns at the non-discounted gross rate. I know what formula I need to use,
.FormulaR1C1 = "='Master file'!RC/(1-'Master file'!RC[-3])"

My problem is that while "'Master file'!RC" is correct, the next reference should only be incremented for Row changes, it should always reference Column M. I have no idea how to phrase this. Any ideas?

Code:
Sub FillSales()
'
' FillSales Macro
' Macro recorded 7/29/2008 by Conde Nast Publications
'

Sheets("Sales Master File").Cells.ClearContents
Sheets("Master file").Select
    Cells.Select
    Selection.Copy
    Sheets("Sales Master File").Select
    Range("A1").Select
    ActiveSheet.Paste
    
lastRow = Worksheets("Sales Master File").Range("A" & Rows.Count).End(xlUp).Row
lastColumn = Range("IV11").End(xlToLeft).Column
For rwIndex = 2 To lastRow
    For colIndex = 16 To lastColumn
        With Worksheets("Sales Master File").Cells(rwIndex, colIndex)
            If .Value  "" Then .FormulaR1C1 = "='Master file'!RC/(1-'Master file'!RC[-3])"
        End With
    Next colIndex
Next rwIndex

End Sub


Hi, The code below is just to illustrate my question. When I use FormulaR1C1, it fills the cell and not just copies the exact formula. What is the principle behind that? How does using R1C1 tell the code to fill and not just copy? Thanks for any explanation.

Code:
Sub MyTest()
With Range("c6")
Range("c6") = .Offset(1, 0).FormulaR1C1
End With
End Sub


Hi Guys,

I am trying to make the below code snippet work to return a date if all arguments are positive. Unfortunately, instead of returning the date, this function returns "". Is there something small I have missed out here?

Should I rather use .FormulaR1C1 = "=TODAY()"? Or is this because I have tried to do the Date function within a WITH statement.Interior?

'If rejected and resolved return todays date
If Left(Cells(ifindrow, iColNum(8) - 1).Value, 3) = "Rej" And Cells(ifindrow, iColNum(8)).Value = "Yes" Then
With Cells(ifindrow, iColNum(8) + 1).Interior
.ColorIndex = xlNone
.Value = Date
End With
End If

Many thanks for your consideration.

Kind Regards,

Stefan

I am having difficulty using vlookup in vba using FormulaR1C1. I am tring to lookup a value from a table using the table O_Rng located in a different sheet. Here is what I have:
HTML Code:
Dim O_Rng as range

set O_Rng = Range("org_tbl!$A$2:$H$59")

sheets("Sheet3").cells(2,9).select
activecell.offset(4,0).FormulaR1C1= "=vlookup(RC[-7],org_tbl!O_Rng,2,false)
I get #Name error.
I have tried changing the lookup table value to
HTML Code:
activecell.offset(4,0).formulaR1C1= 
"=vlookup(RC[-7],Worksheets(""org_tbl"").range(""$A$2:$H$59""),2,false) 
with no luck, yet I can use
HTML Code:
Activecell.offset(4,0) = application.worksheetfunction _
.vlookup(Activecell.offset(4,-7),worksheets("org_tbl").range("$A$2:$H$59"),2,false)
and it gives me the correct result.

Any suggestions, thanks in advance!

Could someone please tell me why this does not work or how to accomplish this:

mRow = 2
mCol = 3
Application.ReferenceStyle = xlR1C1
Worksheets(1).Range("R" & mRow & "C" & mCol).Value = "Test"

I have code that I use FORMULAR1C1 = "Index(...., Match(...))" into an unused cell, then copy and pastespecial for paste value only and then clear the contecnts of the cell I put the Index/Macth in.

Does that clear the link and if not how can that be done in VBA? The file needs to become smaller somehow...

Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement that
gets me the absolute value of a column and lists it into the next column
over. I have it looped because there is no definitive ending point of how
many rows there may be, one day 5, the next day 205. I can get that done,
but what seems the most simple, I just can't figure out. How do I code it
to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

hello happy monday to every one lol... I'm needing a bit of help when it comes to pasting this formula in cell B2 down. Just for simplicity for now NumRows = 100 but i cannot get this formula to work in my worksheet. It needs to change from A2 to A3 etc but I am not too great on using FormulaR1C1. If anyone could give me help it would be fantastic. Thanks everyone!

    Cells(1, 2) = "Deleted Account Names"
    For Row = 1 To NumRows
    Cells(Row + 1, 2).Select
    ActiveCell.Formula = "=IF(LEFT(A2,4)='RSOF',VLOOKUP(A7,'Summary
Total'!A12:B131,2,FALSE),VLOOKUP(A2,Composites!$A$12:$B$132,2,FALSE))"
    Columns.AutoFit
    Next Row


Hi I am sorry for not using the RULES for the codes. I hereby edit my post.
Thanks

Hello
I am tired of reading the cell reference using FormulaR1C1 method in macros. like

Range("L4").Select
 ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Is there any way it can show the normal cell reference
like

Thank u

Toms

It doesn't matter whether I use .formulaR1C1 or .value I just can't get the below syntax right!!!

WRange.Value = "=" & "Ipbmatch(rc[-7]," & (LURange.Address) & ")"

Wrange is a predefined range of cells in one column
IPBMatch is UDF = Public Function IPBMatch(CellRef As Range, SrchVals As Range) As String
LURange is, again, a predefined range.

I'm sure this is straightforward for somebody that knows what they are doing but, unfortunately, clearly I don't!

Any help appreciated.

Regards,

Adrian