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
1 answer

Excel Macro R1C1 formula not retaining values

I have an excel macro that creates a new excel sheet and fills it with a variable number of rows. I have a count of the number of rows stored in a variable. I'm using an R1C1 formula to multiply two cells together based on my row count. In the macro…
Vivek Patel
  • 17
  • 2
0
votes
1 answer

Why is Excel VBA only accepting a relative R1C1 reference in this array formula?

I am trying to add this line of code to my VBA. It adds an array formula to a range of cells: str_Formula = "=INDEX(rng_List,MATCH(1,--(RC1>=rng_A)*--(RC1<=rng_B),0))" rng_Formula.FormulaArray = str_Formula For some reason, the formula only…
jakrooster
  • 31
  • 5
0
votes
1 answer

VBA R1C1 notation Loop reference mismatch

I have a Macro using R1C1 notation that loops over data and produces the difference between the estimated data and actual. I want to be able to produce the percentage difference but I am unable to pick up the reference. My code is: …
Alex
  • 9
0
votes
1 answer

VBA R1C1 formula calculation with cell in another worksheet

ws2.Cells(87, col - 2).FormulaR1C1 = "=R[-5]C[0]-'Another Sheet'!F89" When I run the above code, the result would be, "=AW83- 'Another Sheet'!'F89", which returns an error because the ' between ! and F89. I tried to use " & " but it does not work…
CHsu
  • 17
  • 6
0
votes
1 answer

VBA: R1C1 Notation not being recognised with Range.FormulaArray

I'm trying to put the formula below into a column. The formula works once I remove RC[-1] but I get an object defined error with it included. Is there anyway I can get formula array to work with both r1c1 and a1 referencing? Formula assignment which…
mcseth antwi
  • 131
  • 1
  • 1
  • 9
0
votes
2 answers

Dynamic Range Selection with a relative reference (R1C1) in a Loop

I have an excel sheet (examplary extract below) and would like to achieve following with my VBA code (I am a total newbie to VBA). Go through all rows in coloumn A Calculate Average value for range in column E with identical dates (column…
Aliakbar Ahmadi
  • 366
  • 3
  • 14
0
votes
1 answer

Excel VBA loop using relative references and needing a fixed cell in the formula

I am coding two loops. The first loop sums a column 14 times and then goes through each worksheet in the workbook until they are all calculated. The second loop is more complex. The formula, if you were to enter it manually should be…
Gizzzmo68
  • 3
  • 3
0
votes
3 answers

How to use R1C1 formatted text in a formula

In order to get dynamic formulas to develop through VBA, I had to use INDIRECT(ADDRESS(. I am now stuck with several dozen formulas that look like…
ShNBl84
  • 55
  • 1
  • 12
0
votes
1 answer

Excel VBA R1C1 formula for SUM xlUp

This code will sum the 6 cells above it in Column P of the last row+1 of the data dump. Sheet2.Range("A1").End(xlDown).Offset(0, 15).FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)" I need help with the R[-6]C part. The dump can have variable rows and I want…
Dasal Kalubowila
  • 103
  • 5
  • 22
0
votes
1 answer

Variable String in Formula R1C1

I am trying to create a formula using R1C1 format that will change based on a string containing a variable. I have tried creating the string and inputting it in the formula, as well as creating the string in the formula and none seems to work. Below…
Campbesj
  • 3
  • 1
  • 2
1 2
3