When I refer more than one column using Formular1c1

Asked

Viewed 211 times

0

good morning!

I’m having problems when referencing more than one column in excel using VBA, for example:

I make a selection of columns Stock! A:G and create the macro, and generate for me Stock! C[-6]:C, I don’t understand how he behaves when I migrate to Formular1c1 referencing.

someone can help me in this matter?

thank you in advance!

excel vba microsoft spreadsheets

  • Did you write the macro or are you doing it in your hand? How did you happen to get to Stock! C[-6]:C?

1 answer

0

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

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

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

Note that when using numbers between keys ([]) on the model R1C1 vc says selection expands left or right (in case of columns) from an active cell.

If you want to stop recording with relative references and take the absolute address of the recorded intervals, disable the resource in the tab Developer excel:

Usar Refências Relativas

Browser other questions tagged excel vba microsoft spreadsheets

You are not signed in. Login or sign up in order to post.