When I refer to more than one column using FormulaR1C1

Navigation
0

Good morning!

I'm having trouble referencing more than one column in excel using VBA, for example:

Stock! A: G and I create the macro, and it generates for me Stock! C [-6]: C , I did not understand how it when I migrate for FormulaR1C1 referral.

Can anyone help me with this?

Thank you!

    
asked by anonymous 30.03.2017 / 16:01

1 answer

0

Although not very well detailed and explained what is the problem and what you want, I conclude the following:

It seems to me that you have recorded the macro and you are using relative references , especially indicated by the keys -6]: C is picking up the column where the active cell (represented by C ) is standing up to 6 columns to the left of the column of the selected cell (represented by C [ 6] ). Other ways to represent this same range with R1C1 would be:

  • If the active cell is in column A: Stock! C: C [6]
  • If the active cell is in column B: Stock! C [-1]: C [5]
  • If the active cell is in column C: Stock! C [-2]: C [4]
  • If the active cell is in column D: Stock! C [-3]: C [3]
  • If the active cell is in column R: Stock! C [-4]: C [2]
  • If the active cell is in column F: Stock! C [-5]: C 1
  • If the active cell is in column G: Stock! C [-6]: C (same as yours)
  • No relative reference : Stock! C1: C7 (handle 1st to 7th column)

Note that when using numbers in braces in the R1C1 model you say that the selection expands to the left or right (in the case of columns) to from an active cell.

If you want to save with relative references and get the absolute address of the saved intervals, disable the feature in the Developer tab of Excel:

    
30.03.2017 / 20:21
Add + 1 to a variable and access different pages depending on the value Copy cells to another worksheet if it does not exist