Free Microsoft Excel 2013 Quick Reference

R1C1 Notation in VBA

Excel 2000

Is it possible to use R1C1 notation in VBA? I'm using it in cell
formulae notation.

Range("A1").Select of course works.





Fred Holmes

Post your answer or comment

comments powered by Disqus
I'm trying to find a way to implement a macro that would allow me to enter the SUMIF function into a bunch of cells in a worksheet.

Right now, my code looks like this:

    'other calculations
    c_shift = num_SKUs * 2
    Dim entry_col As Integer
    For total_row = 6 To 99
        For column_ = 1 To num_SKUs
            entry_col = (column_ * 2) + 13
            cells(total_row, entry_col).formulaR1C1 = _
                SUMIF(R6C13:R99C13, RC[- " & entry_col & "+1, R6C[-1]:R99C[-1])
        Next column_
        Cells(total_row, 14 + c_shift).FormulaR1C1 = _
            "=SUMIF(RC[-" & c_shift & "], " & "Before" & ", RC[-1])"
        Cells(total_row, 15 + c_shift).FormulaR1C1 = _
            "=SUMIF(RC[-" & c_shift & "], " & "After" & ", RC[-1])"
    Next total_row
Of course, it gives me a error in the bolded line, saying that there is a "Application-defined or object-defined error". I don't know the right way to specify a range using R1C1 notation (except for doing Range(Cell(), Cell()... but I don't think I can do that in a formula)

What should I do?

Thanks for all the help

Hi there,

I wonder if anyone could help me please as I am completely stumped!

I have a an R1C1 formula I have written in my macro and I copy the formula into several cells. However I would like to "fix" the column. Whereas in a normal excel formula I would just put the dollar sign in front of the column, how do I do the equivalent in VBA?

As an example, please see formula below:

Cells(3,4).FormulaR1C1 = "=R[1]C[-1]-""0:0:1"""
Many thanks for your help.


I am trying to get VBA to stop changing a large(double) number into scientific notation. The reason I need this is because I need the exact number to do a web query and when VBA changes the number to scientific notation the query goes to an error page on the website. The number I have is 634703040000000000, however when I put this into a module in VBA it changes to 6.3470304E+17. Is there any way to stop this?


I have designed a form that takes in a lot of information and then saves that information to a sheet called 'Data'. Each entry is contained on it's own row and there are some formulas involved. To simplify things, I wrote the formulas in R1C1 notation in VBA.

The problem occurs when the macro saves the formulas to their designated cells. Each and every one of them comes up with the #NAME? error, even though the syntax of the formula in the Formula Bar is perfect. I can fix this by clicking into the Formula Bar as if I am going to edit the formula, and immediately pressing Enter. The formula does not change, but suddenly it works. What can I change in my code in order to get this to work?

Private Sub
    With Worksheets("Data")
        FinalRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A" & FinalRow + 1).Value = txtCharacterName.Value
        .Range("B" & FinalRow + 1).Value = txtInitiative.Value
        .Range("C" & FinalRow + 1).FormulaR1C1 = "=20-(RC[-1])"
        .Range("D" & FinalRow + 1).Value = txtFort.Value
        .Range("E" & FinalRow + 1).Value = txtReflex.Value
        .Range("F" & FinalRow + 1).Value = txtWill.Value
        .Range("G" & FinalRow + 1).Value = txtListen.Value
        .Range("H" & FinalRow + 1).Value = txtSearch.Value
        .Range("I" & FinalRow + 1).Value = txtSpot.Value
        .Range("J" & FinalRow + 1).Value = txtSTR.Value
        .Range("K" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("L" & FinalRow + 1).Value = txtDEX.Value
        .Range("M" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("N" & FinalRow + 1).Value = txtCON.Value
        .Range("O" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("P" & FinalRow + 1).Value = txtINT.Value
        .Range("Q" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("R" & FinalRow + 1).Value = txtWIS.Value
        .Range("S" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("T" & FinalRow + 1).Value = txtCHA.Value
        .Range("U" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)"
        .Range("V" & FinalRow + 1).FormulaR1C1 =
        .Range("W" & FinalRow + 1).FormulaR1C1 = "=RC[-9]+RC[38]+RC[44]+RC[50]+RC[56]+RC[58]+RC[59]"
        .Range("X" & FinalRow + 1).FormulaR1C1 = "=RC[-2]-RC[-11]"
        .Range("Y" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("Z" & FinalRow + 1).Value = txtArmor.Value
        .Range("AE" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("AF" & FinalRow + 1).Value = txtArmorEnhance.Value
        .Range("AK" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("AL" & FinalRow + 1).Value = txtShield.Value
        .Range("AQ" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("AR" & FinalRow + 1).Value = txtShieldEnhance.Value
        .Range("AW" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("AX" & FinalRow + 1).Value = txtNatural.Value
        .Range("BC" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("BD" & FinalRow + 1).Value = txtNaturalEnhance.Value
        .Range("BI" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
        .Range("BJ" & FinalRow + 1).Value = txtDeflection.Value
        .Range("BO" & FinalRow + 1).FormulaR1C1 = "=SUM(RC[1]:RC[5])"
        .Range("BP" & FinalRow + 1).Value = txtDodge.Value
        .Range("BU" & FinalRow + 1).FormulaR1C1 = "=IF(ISBLANK(RC[3])=FALSE,RC[4],RC[2])"
        .Range("BV" & FinalRow + 1).Value = cboSize.Value
        .Range("BW" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Tables!A2:C10,2,FALSE)"
        .Range("BZ" & FinalRow + 1).Value = txtPrestige1Name.Value
        .Range("CA" & FinalRow + 1).Value = txtPrestige1.Value
        .Range("CB" & FinalRow + 1).Value = txtPrestige2Name.Value
        .Range("CC" & FinalRow + 1).Value = txtPrestige2.Value
        If chkWisdom = True Then .Range("CD" & FinalRow + 1).FormulaR1C1 = "=RC[-63]"
        .Range("CE" & FinalRow + 1).Value = cboType.Value
        .Range("CF" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Tables!E2:F4,2,FALSE)"
        .Cells.Sort Key1:=Range("CF2"), Order1:=xlAscending, Key2:=Range("A2") _
            , Order2:=xlAscending, Header:=xlYes
    End With
Unload Me
End Sub
Also, I am confused as to why the sort command at the very bottom gives me an error as well.


I am writing a macro that completes a number of operations and formatting with my worksheet. One issue I am having is with trying to create data validation with the data. I have a column, K, where I am trying to create the DV. I have column H, which contains titles...there are named ranges in another sheet that are based off of these titles (i.e. one of the Titles is Concrete Section, while I have a named range called nr_Concrete_Section). What I want is for the data validation in column K to be based off of the named range, derived from the title in column H. I can do it manually by just doing...

=INDIRECT("nr_"&SUBSTITUTE(H6," ","_")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is that I can't get it to work for all the rows when it runs in the macro, because I don't think I can use R1C1 notation with data validation? I have been searching around for a while and can't find anything. This is what my code currently issue is with that $H$6 term, I want it instead to be something like RC[-3] (which would refer to that row's column H), but it gives me an error when I do that.

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
    xlBetween, FormulaR1C1:="=INDIRECT(""nr_""&SUBSTITUTE($H$6,"" "",""_""))" 
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .InputTitle = "" 
    .ErrorTitle = "" 
    .InputMessage = "" 
    .ErrorMessage = "" 
    .ShowInput = True 
    .ShowError = True 
End With 

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


I want VBA macro in Excel to write a formula in R1C1 in a specific cell, e.g.

ActiveCell.FormulaR1C1 = "=IF(R[-15]C="""",0,1)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, for specifying cell location I would like to use variables defined previously in the macro. The example above refers to a cell 15 rows above and in the same column as the active cell (R[-15]C). Say I want to let the location depend on a variable 'counter'=-15. I would like the formula to look like this:

ActiveCell.FormulaR1C1 = "=IF(R[counter]C="""",0,1)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In this format however it does not work (it seems to require a number in the []). Enyone knows how to fix this?

Many thanks for help!


hi there,

I have just started to learn VBA and am a little confused by the code of the macro that I have just recorded. I just wrote the formula : =left(A1,3) - to return the first three letters of cell A1. The code that came up was :

ActiveCell.FormulaR1C1 = [color=blue] "=LEFT(RC[-1],3)" [/color] 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second row of the code : "=LEFT(RC[-1],3)" .

Is this R1C1 notation. I didnt expect the code to look like this.

Thanks a lot guys.



I'm trying to figure out how to use R1C1 formula style to represent the entire column for a calculation.

How do I turn the following array cell formula into VBA with R1C1?

I guess in VBA, it should be something like...
Worksheets("test").Range("F2").FormulaArray = _

I am having trouble figuring out how to write a SUMPRODUCT formula in VBA
that multiples an array of cells which I would like to reference in relative
R1C1 terms by an array of cells that I would like to reference in absolute
terms on another sheet. The first part of my formula is fine, but I want the
cells from the DI_NAM sheet to always refer to rows 5 to 9. The column must
be the same as the first array. If there is a way I could put a variable
into the reference, that would work, but I can't figure out how to do this.
I need to replicate this formula as part of a block of data that gets created
in a do loop.

Selection.FormulaR1C1 = "=SUMPRODUCT(R[-7]C:R[-3]C,DI_NAM!R[-17]C:R[-13]C)"

This formula gets input across 36 columns as I am working with 36 months of

I am a novice at VBA, so if anyone knows how to do this, I would really
appreciate the help!

Does anyone know How I can correct R1C1 formulas being returned in there R1C1 formula ? Ocassionally the formula is ruturned correctly, the rest of the time the R1C1 notation is returned to the cell. They are simple formulas, Ive even tried setting time delays with no success to the formulas being input. Any ideas??
Once the formula is input a large range is filled for the formulas

Here is the code

Range("I2").Select 'Quantity
ActiveCell.FormulaR1C1 = "=RC[-1]" 
Selection.NumberFormat = "0" 
ActiveCell.FormulaR1C1 = "=R[-1]C[-1]*(-1)" 
Selection.NumberFormat = "#,##0" 
ActiveCell.FormulaR1C1 = "=R[-2]C[-4]*(-1)" 
Selection.NumberFormat = "General" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and Here is what it returns incorrectly
Last column has the formulas(though I cant' get to to view correctly)

6,600.00  330.00  414.12  12,000      =RC[-1] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
here it is when it is correct

6,600.00 330.00 	414.12 	12,000	12000 

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

This is the code that I get when I record a macro and perform the task using excel:
Sheets(1).Cells(4, 100).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

But I would like to sum the sheets like this:

Sheets(1).Cells(5, 100) = sum(Sheets(1).Cells(5, 96), Sheets(1).Cells(5, 99))

But I get an error message saying: "wrong number of arguements or invalid property assignments."

how can I use the sum function in VBA with a simialr notation?

Thank you in advance,

I know I have seen the answer to this, but I can't find it through searching.

I have defined coli as the count of columns in the range I need to sum. This range will change over time.

I have populated the columns with a formula, and I want the rightmost column to contain a sum.

I tried doing something as simple as trying to use coli as the # of columns to move in R1C1 notation, but that doesn't work.

Any assistance is greatly appreciated.

    .FormulaR1C1 = "=SUM(RC[-coli]:RC[-1])" 
    rngTotals = rngTotals.Value 
    .Style = "Currency" 
End With 

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

I'm frustrated.
I get that there are many ways to reference cell addresses(A1, RC, offset from an active cell, etc.). But when to use which
Putting one of these into a VBA formula I don't get. I'm almost ebarassed to even broach the subject to such knowledgable people

Okay, after looking through an awful lot of posts, what does the "!" mean in the following: !R1C1:R44C5 Is it an absolute vs relative indicator?

I'm working on a SUM(range) formula but although Excel macro words can be used in VBA, I can't find an explaination in VBA Help. Its index stops at "Submit". The VBA help lists it as a worksheet function but not how to use in a VBA module.

I NEED TO READ - Please point me in some direction (online hopefully).

I note the "Cells" property and it has the column, a comma, and the row. That would seem ideal to use (a variable for the row))

This is what I'm attempting in a very crude manner
Qty=sum(cell($c, $row) to (cell($c, row variable)))

I've dim'd the variables. Running it, the "sum" is highlighted and the error is "Sub or Fuction not defined"

While I'd like the answer, I'd also like to be pointed to a source for putting things together.

Hope my rambling has at least lead to a and that I have not taken up too much forum space and your time. Please don't kick me out. I'm trying! At 58 some of the brain functions may be gone.

Hi, I am trying to get data from a workbook called "MasterTable.xls" into a worksheet "Demographics" of workbook "Country Specific Parameters.xls" in VBA. But, VBA does not seem to resolve the Workbook name with spaces. Please guide what should I do? Thanks in advance. Below is the code that I wrote to get this done

With Range("['Country Specific Parameters'.xls]Demographic! K6")
.FormulaR1C1 = "=VLOOKUP(R6C11,[MasterTable.xls]Sheet1! R1C1:R300C10,10,FALSE)"
.Value = .Value
End With

I am building a macro that is being forced to use R1C1 notation due to the fact that my macro is based entirely on the current position of the ActiveCell.

What I need this part of my code to do is in essence =LOOKUP(RC[-1],$A$1:$AA$1)... however I know I cannot mix R1C1 and A1 notations. The LOOKUP is cross referencing a date versus another table to find what policy term the date falls under. Here is the code I have been able to figure out that will at least work for the first cell.

However, I need to autofill the formula down. When I do that, my reference range goes completely out of whack. Is it possible
to keep the reference range in-tact while autofilling?

Here is my entire code.

Sub PolicyFill()
Dim LR As Integer
LR = Cells(Rows.Count, ActiveCell.Offset(0, -1).column).End(xlUp).row
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-1],R[" & (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) & "]:R["
& (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) + 26 & "])"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-1]) & "" - "" & YEAR(RC[-1])+1"
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column))
ActiveCell.Offset(0, 1).Select
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column))
End Sub

I am trying to use the R1C1 reference style in my code but intead of numbers I want to use variables. Right now I have the line:

ActiveCell.FormulaR1C1 = "=MIN(R" & intRightR & "C" & intRightC & ":R" & intBottomR & "C" & intBottomC & ")"

and depending on an evaluation of two numbers, the variables will change and the loop will start over. The only problem is that this line of code is not working. I saw Derk's post of trying something similar to:

Range("A1").formulaR1C1="=SUM(R" & row1 & "C" & col1 & ":R" & row2 & "C" & col2 & ")"

Could anyone help me out with this. Thanks in advance!

Trying to create a cell formula from VBA that can return a cell reference or a text value. Since the formula I'm creating is bracketed by quotes (""), I can't put the text value (NA) in quotes. Is there a special wildcard to use to accomplish this?

Thank you,

Tony R

Another post got me interested in this. It's desired to put the sum at
the bottom of columns C through E. I want to put the sum there, not a
formula. I thought some kind of R1C1 notation would be necessary, but
I got this method from help. The notation seems odd but it works well.
Is this the best way to do it?
TIA, James

Sub SumCols()
Dim k As Integer, LastRow As Long, myRg As Range
For k = 3 To 5
LastRow = Cells(65536, k).End(xlUp).Row
Set myRg = Range(Cells(2, k), Cells(LastRow, k))
Cells(LastRow + 1, k) = Application.WorksheetFunction.Sum(myRg)
Next k
End Sub

Hi All,

I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.

How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.
Here is the formula:
And here is what I have so far in VBA:
With Range("F4:F50")
    .FormulaArray = "=COUNT(1/FREQUENCY(IF('NZ Container Stock'!R2C7:R100C7=RC[-4],'NZ Container Stock'!R2C1:R100C1),'NZ
Container Stock'!R2C1:R100C1))"
End With
It seems like the column B reference is not changing and is staying at B4.

Any help is appreciated.



I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong. Can anyone help?


I just recently began to work with macros that use the R1C1 notation and am running into syntax trouble trying to convert from A2 to R1C1. In my spreadsheet I implement an IF logic test as follows:

However, I can't seem to express this in my macro in R1C1. The logic behind the test is if A2 is blank report a 1, if not then report a zero. Any help would be greatly appreciated.

Hey all,

I have a fairly simple nested IF function judging a couple of ISBLANKs as well as some LEN and RIGHT. The problem is that I'd like to put it in VBA. Can do that easily. Here's the catch: I'd like to make the column references dynamic in case the columns get re-arranged. So if "Title 1" gets moved to column C, I'd like the formula to use column C. Does that make sense? Here's the formula in a regular cell.


The code is easy, but making it so the column can shift is a bit harder. I was trying to include Match("Title 1",$A$1:$Z$1,0) inside, but that doesn't return letters and it gets too complicated for the macro to record.

Any help?

Thank you so much. I appreciate the help.

Dear Expert,

I would like to use Excel recorder to name a range in VBA.

The table may have different number of rows every day in a file. It can start at Row 13 one day, maybe starts as Row 17 the other day.

Table column starts with "Age Bucket" in Column A.

Using recorder, it has to define a fixed address which may be true for one day, but wrong for next day.

In this case, I was forced to set the range and name it as "DATA"


Is it possible to select the data in table after column header "Age Bucket" and name it as "Data" in VBA?

Thanks for help.

Sub Macro1()

Application.Goto Reference:="R1C1"
Cells.Find(What:="Age Bucket", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
ActiveWorkbook.Names("Data").Comment = ""
End Sub

I have been writing functions in vba for a template for designing heat exchangers. It returns #name errors when i try.
I have several functions I think i may have a problem with most of them. but they are written in such a way that
they depend on each other so that if the first returns an error the rest wont show anything.
The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.

 'Correction Factor calculations for the LMTD
 'source Robert R Serth, heat Transfer principles and applications.
 'For any number of shell side passes and any even number of tube side passes as follows
 'LMTD is calculated as
 ' N is the nymber of shell side passes
Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double 
     'Correction Factor calculations for the LMTD
     'source Robert R Serth, heat Transfer principles and applications.
     'For any number of shell side passes and any even number of tube side passes as follows
     ' N is the nymber of shell side passes
     'R P and alpha are ratios used to calculate the correction factor
    Dim R As Double, P As Double, S As Double, alpha As Double, n As Double 
    Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the
     'Where  Ta (Tis)= inlet temperature of the shell-side fluid
     'Tb (Tos) = outlet temperature of the shell-fluid
     'ta (Tit= inlet temperature of the tube side fluid
     'tb(Tot) = oulet temperature of the tube side fluid
    R = (Tis - Tos) / (Tot - Tit) 
    P = (Tot - Tit) / (Tis - Tit) 
    n = N1 * N2 
    alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) 
    If R = 1 Then 
        S = P / (n - (n - 1) * P) 
        num = (S * 2 ^ 0.5) 
        den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5))) 
        F = num / den 
        alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) 
        S = (alpha - 1) / (alpha - R) 
        num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S)) 
        den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 /
        F = num / den 
    End If 
    Calcfc = F 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any body with any idea.

No luck finding an answer? You could always try Google.