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
0
votes
0 answers

Google Apps Script SUM rows into one row for every column

I have a list of rows in my Google Apps Script stored in a list : var index = [5.0, 11.0, 20.0, 23.0, 33.0, 40.0, 44.0, 49.0, 52.0] This list updates everyday and may look like : var index = [6.0, 11.0, 20.0, 50.0, 56.0, 90.0, 112.0] etc I would…
Julie-Anne
  • 23
  • 6
0
votes
0 answers

VBA FormulaR1C1wrong column from column AA

I am using the below code to add a formula to a cell: Cells(1, 1).FormulaR1C1 = "=RC" & Range("Col2").Column & "-RC" & Range("Col28").Column & "-RC" & Range("Col54").Column What I'm expecting in Cell A1: =B1-AB1-BB1 What happens…
Änna
  • 1
  • 1
0
votes
0 answers

Using formulas within RC formulas

I'm creating a spreadsheet and need to find a way to change a formula based upon certain selection criteria. The user needs to select one of 4 options. The options are Class: 4, 5, 6 or 7. Based upon the class selected, calculations further down are…
Mark Goodhead
  • 15
  • 5
0
votes
0 answers

Is it possible to refer a whole column of another sheet in .formular1c1?

I have this question based on a question asked in this link For example, the formula in cell C1 Sheet1 is =VLOOKUP(A1,Sheet2!A:B,2,FALSE) The code I've tried which work is only the "test-5" Sub test() 'test-1 Range("C1").FormulaR1C1 =…
karma
  • 1,999
  • 1
  • 10
  • 14
0
votes
1 answer

Double click a cell in a table and filter another table based on R1C2

I'm trying to create a small table that you can double click on any cell and filter a second table based on the values in row 1 (AYEAR) and column B (AMONTH) to return the breakdown of the total value from my original table. How do I express this…
Frinkenstein
  • 45
  • 7
0
votes
1 answer

passing formula from vba to cell

I am trying to pass formula in Excel cell using Excel VBA: Mt_1 = "trade-508-cd" bpTargetRange.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""*""&Mt_1&""*"",RC[-1])),""1"",""0"")" When running the above the cell is having…
selet
  • 31
  • 3
0
votes
1 answer

Excel VBA: Formula R1C1 with absolute reference to multiple columns from variables

I have the following issue: I am getting various Excel sheets with more or less the same headers (they are not always the same), but almost always in a different order. However, I can get the index of the column by searching for the header name.…
user56591
  • 473
  • 1
  • 4
  • 11
0
votes
1 answer

VBA Run-time Error 1004 for Activecell.FormulaR1C1

So, I have created a pretty extensive macro and I am updating/appending to it. Long story short, the following line triggers the run-time 1004 error, but I do not know why. ActiveCell.FormulaR1C1 = _ "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" &…
Frosty_Fraz
  • 47
  • 1
  • 11
0
votes
0 answers

Range in R1C1 format Excel VBA

The range i want to use is in the below R1C1 format. How do i use it to put a value into the cell. Myrange = sheets(1).range("A1").address(referencestyle:=R1C1) Myrange = "value" (not working.) The range is actally used in a loop with many…
Hound
  • 1
  • 3
0
votes
1 answer

VBA Transpose From Specific Number of Cells Above

I've been working on a VBA code, this is only a minor part of my project for reviewing accounting transactions. The code not included inserts blank rows where there have been transactions using multiple account names. I want to transpose the…
John Beauchamp
  • 3
  • 2
0
votes
1 answer

Using Parentheses within R1C1 Formula

I am having issues with the R1C1 function. I am trying to search a column (A) in a worksheet and, if a value is found, paste a formula into the cell 8 rows to the right. The formula is using the R1C1 function, as it references different cells each…
Kramer328
  • 1
0
votes
1 answer

VBA - Maintain cell formula integrity

I am having a difficult time inputting an Indirect formula into my code. I understand that I can use a FormulaR1C1, however that creates a formula in the cell that is fairly static and is not too kind when adding rows after I run my code. I'd like…
A Cohen
  • 458
  • 7
  • 26
0
votes
1 answer

R1C1 Formula result gives twice the given variable

I want to normalize some data and want this normalized data to change dynamically when the data changes, that's why I want to put Formula in Cells that reference different Cells. But that's not really relevant to the problem because even the…
Deglupta
  • 25
  • 4
0
votes
1 answer

VBA Debug - Run-time 1004: R1C1 Formula Not Calculating

I'm trying to write some VBA scripting that embodies an Excel formula, but keep getting an error and can't seem to find a solution anywhere. I think that what might be causing the problem is that I have an R1C1 formula that includes a +1. I can't…
Mesanic
  • 5
  • 1
  • 2
0
votes
1 answer

Why Does Repeating a VBA FormulaR1C1 Offset The Reference By Row?

I have seen this a couple of times and usually can work around it, but I have some international users that require me to figure out something different. I have a row that contains several formulas unique to each column. I wish to duplicate across a…
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
1
2
3