Stack Overflow
Stack Overflow

Questions tagged [excel-r1c1-notation]

Questions about Excel formulas that use the R1C1 notation for describing absolute and relative ranges.

40 questions
4
votes
2 answers

Conditional formatting using the INDIRECT function fails with boolean AND or OR or with cells containing formulas

I have the following function for checking whether column L contains the word "completed" and I use INDIRECT to be able to color the whole row with Conditional Formatting: =INDIRECT("l"&ROW())="completed" This function works. However, I need to…
Abel
  • 56,041
  • 24
  • 146
  • 247
2
votes
2 answers

Why can't a single-letter UDF name use C or R?

I can write the following valid VBA functions: Public Function C() As Long C = 5 End Function Public Function R() As Long R = 6 End Function But I can't use them in an Excel formula, without qualifying them with the VBA project or module…
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
1
vote
2 answers

Error using R1C1 notations in Formula on Google Script

I've encountered some issues on my new Macro. In fact, I'm translating a VBA Macro in Google Apps Script language but I don't understand something. Instructions : feuille_31.insertColumnAfter(8); // Inserting a new column …
thinkpad23
  • 11
  • 3
1
vote
1 answer

VBA Last Row and Column from different sheet in R1C1

Find my code at the end of the question. I am trying to automate a daily process. Every day, a new row is added to the ITR file and I need to enter the new value of that column in another sheet. Is there a way I can create a macro which can select…
Ioannis Loulloupis
  • 21
  • 4
1
vote
0 answers

vba Sumif with variables and R1C1

I'm trying to run the below code and it gives me Run-Time Error "1004" Application-defined or Object-defined error Every Single Time!! Attached is a snippet of the code, any suggestions what's wrong? (The numbers in the Range(Cells( * ) sections…
Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
1
vote
0 answers

Set a variable on a FormulaR1C1

I'm trying to code a variable R1C1 formula but I don't know what I'm doing wrong. I searched a lot but couldn't find any answer applicable to my case. Sub Step17() Dim Final As Integer ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[&Final&]C)" End Sub I…
Daniel Jaramillo
  • 11
  • 1
1
vote
1 answer

Excel VBA how to keep a variable cell in a R1C1 formula

My problem is that I can't figure out a way for the formula not to immediately evaluate the cell number, while I would rather want it to keep it variable. I'm currently working on a code that checks the value of an arbitrary cell in the workbook…
Matteo Ghiringhelli
  • 11
  • 1
1
vote
2 answers

Issue with FormulaR1C1

I need a little help with the below formula in VBA: =IFERROR(VLOOKUP(J3,'Int Data'!D:J,7,0),"") I've tried to run the following: Range("AP2").Select ActiveCell.Formula = "=IFERROR((VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0),"")" …
SeT
  • 224
  • 2
  • 13
1
vote
1 answer

activecell.formula with RC notation and vlookup not working vba

I have this code and I cannot get it to work with RC notation ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],ARTICULOS!$A:$I,4,FALSE)" The value that I'm looking for is 1 column after the "activecell".
JUAN CARLOS PERON
  • 17
  • 6
1
vote
1 answer

VBA RC choosing another row

two worksheets countifs for totaling the data Range("F3").FormulaR1C1 = "=COUNTIF(data!C2,numbers!RC14)" from C2 i get all of column B in data. perfect. from RC14 i get column N in numbers...but row 9. i need N3. im sure its probably something…
TideRunner
  • 9
  • 1
  • 5
1
vote
2 answers

Cell formula using a variable

I'm using the Excel VBA Editor (I have both Excel 2007 and Excel 2016). I have a variable parameter i, all the others are fixed. Could you please say me how I can put a formula in a cell Cells(i, 2)? using variables from my macro (j1, j2, i1)…
eddie
  • 415
  • 4
  • 13
1
vote
1 answer

Attempting to use FormulaR1C1 referencing multiple cells

I am attempting to use the FormulaR1C1 property in VBA but keep running into a runtime error 1004. My code is as follows. ActiveCell.FormulaR1C1 = "=R[0]C[-1] * (1 + =R" & cellcount & "C1)" Any ideas?
Sam Mcdonald
  • 25
  • 3
0
votes
1 answer

Calculated sums in R1C1 format cell reference

I have the below formula I am trying to get to work. "=XLOOKUP(1,MMULT(--(R1C41:R168C54=RC[-1]),TRANSPOSE(COLUMN(C[-16]:C[-3])^0)),R1C40:R168C40,"""")" As above it works, however I am trying to make it relative due to changing nature of input. The…
Zeetek
  • 1
  • 1
0
votes
1 answer

VBA Excel FormulaR1C1 "Application Defined or Object Defined Error"

ı try to use excel formulas ın vba , use record macro to defıne correct formulas then try to change accordıng to each "0" line to calculate subs, max value but get defıne error . the problem ıs FormulaR1C1 , couldnt wrıte correctly . use excel…
Ebrar Emin Gumruk
  • 1
0
votes
0 answers

Extract Value from R1C1 formula

I am trying to sum up entire rows by column. IE: I have Rows 4:6 that I am working with. I want it to sum A4:A6 into A7, B4:B6 into B7, C4:C6 into C7, etc, up to a predetermined Column. I need the ability for the range to be dynamic as well. I came…
J71
  • 1
  • 2
1
2 3