In excel macro editor what is r1c1.?

Question

I am trying to run a macro in excel where on one worksheet a cell or .range = another cell off the main page but I don't know what r1c1 is.ex. ActiveCell.FormulaR1C1 = "=Main!R[2]C[4]"

Range("J2:K2").Select

I need the "=main!R[2]C[4]" to = cell A6. why can't i just do "=main!"A6"

Answer 1

R1C1 = A1

R = Row

C = Column

R1C1 ia another way to tell Excel which cell(s) to which you are referring.

Answer 2

RxCy means the row and column of the cell you need, so you *can* do Main!R2C4 (if the sheet is named Main).  R1C1 means "ThisSheet"!R1C1 - iow, if you don't specify the sheet, a formula refers to the sheet it's on.  If you do it refers to the sheet you specify.

So just use Main!A6

Answer 3

There are a number of ways you can do what you are trying to do.  What is throwing you off is the macro recorder throwing code at you and you not understanding VBA well yet.  So I'll explain a few things so you understand a little better.

ActiveCell.FormulaR1C1 basically states that you are changing the formula in Excel's formula bar for the cell your cursor is on (the active cell).  Notice that it says R1C1 at the end of Formula.  That means you are entering your formula in Row and Column notation.  If you go to the General tab under Options, you can check R1C1 reference style to see what R1C1 is like.  It basically turns the column letters to numbers.  It works somewhat like the A1 reference style, but it is a little different.  Brackets are used to denote Relative referencing.  If brackets aren't used, that denotes non-relative referencing, so that is the same as using the $ symbol in the A1 style.

Here's what your macro code is saying:

ActiveCell.FormulaR1C1 = "=Main!R[2]C[4]"

Set the cell's formula that I have the cursor on to the cell on the worksheet called Main that is 2 Rows down and 4 Columns to the right of the active cell.

Remember the absence of brackets means that it isn't relative, it's absolute referencing.  So for A6, you'd want Row 6, Column 1.  So your formula would look like this:

ActiveCell.FormulaR1C1 = "=Main!R6C1"

The Macro recorder was set up to record macros in the simplest way possible.  For writing macro code, it is actually simpler when writing more complex code to stick to the R1C1 reference style because it is easier to move up a number than it is to move up a letter.  To move up a number, all you have to do is add.  To move up a letter you have to reference and convert and do a ton of other junk.

Now if you want to set your formula up in A1 reference style instead of R1C1 then you should use .Formula instead of .FormulaR1C1.  For example:

ActiveCell.Formula = "=Main!A6"

The Range("J2:K2").Select statement you don't need.  That just tells Excel to select the range J2 to K2 on the active sheet.  In macro programming you don't need to select stuff to change the values.  Selecting stuff only slows your macro down and makes it run slower.

If you don't want a formula in cell A6, you can set the active cells value to A6 without using a formula.  Here's how you'd do it.

ActiveCell.Value = [A6]

Stating [A6] in VBA code is the same as stating Range("A6").  Doing it the way I did is the short notation for it so you can write the code faster.  Also, for ranges the Value property is the default property.  So you don't have to type in .Value, but it is a good idea to do so.  Doing so helps other people know what property you've changed.

If you have further questions, feel free to ask.  You might want to avoid using ActiveCell too.  Try using the actual cell reference.  So like if you are wanting to set cell B10 on the active worksheet to cell A6 on the Main worksheet, then use the code:

[B10] = [Main!A6]

Remember, the value property is the default property so it doesn't need stated, but that would be the same as:

[B10].Value = [Main!A6].Value

Or the same as:

Range("B10").Value = Range("Main!A6").Value

The difference is the length and just having to type less.