We help IT Professionals succeed at work.

Access VBA code meaning

Conernesto
Conernesto asked
on
I have the below piece of code in a module. The code is run from Access and it's applied to an Excel spreadsheet. Can yo please describe what below code means as I may need to make a change?

 .Cells(44, C).Value = "=R[1]C-SUM(R[-8]C:R[-1]C)"
Comment
Watch Question

Analyst Assistant
Commented:

It's putting a formula in a cell, which cell is hard to determine without knowing what C is or if there is a further range reference.


The formula itself can be interpreted as such:


Subtract the sum of the 8 cells above the destination cell from the value in the cell below the destination cell.


For example if the formula was to go in cell H17 it would look like this:


=H18-SUM(H9:H16)



Author

Commented:
C appears to be the start column. What does the R stand for?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
R[1]C

R stands for Rows
C stands for Columns

in this form of notation the values are the offset from the current cell.  This is often referred to as R1C1 notation, see: https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

Author

Commented:
Thank you very much.
NorieAnalyst Assistant

Commented:

Conernesto


As Daniel has explained the R and C in the formula refer to rows and columns, but the R here,   

  
  .Cells(44, C).Value =
   

is a variable and represents the column the formula will be placed in.


Author

Commented:
Great. Thank you.