1

What is the correct syntax for telling VBA

"consider the value in sheet_1 in the cell that has offset (0,i) from the activecell" ?

I tried with

Sheets("sheet_1").ActiveCell.Offset(0, i).Range("A1").Value

but it gives me

runtime error 438

Please note that my aim is to do:

If Sheets("sheet_1").ActiveCell.Offset(0, i).Range("A1").Value = Sheets("sheet_23").ActiveCell.Offset(0, i).Range("A1").Value Then

'do something
Tms91
  • 3,456
  • 6
  • 40
  • 74
  • Have you renamed your sheet to `sheet_1` with an underscore? – Samuel Everson Apr 22 '20 at 10:51
  • 2
    `ActiveCell.Offset(0, i).Value` – SJR Apr 22 '20 at 10:52
  • @SJR I need the sheet dfinition in the same line, because I want to confront cells in different sheets at the same time – Tms91 Apr 22 '20 at 10:53
  • 4
    You can't have an active cell in more than one sheet. – SJR Apr 22 '20 at 10:54
  • but from the gui I can see the cursor in both sheets.. – Tms91 Apr 22 '20 at 10:55
  • If you are planning on using `ActiveCell` you would need to `Activate` the relevant `Worksheet` (and potentially cell) before using it... it's recommended to avoid this if you can and I don't believe what you want is doable in your requirements... – Samuel Everson Apr 22 '20 at 10:58
  • 3
    Perhaps you should explain what you're trying to achieve. You could extract the address of the active sheet on sheet1 and refer to the same cell on sheet2- is that the sort of thing you're after? – SJR Apr 22 '20 at 11:03
  • @Samuel Everson yes, I renamed the sheet. – Tms91 Apr 22 '20 at 16:44
  • 1
    @SJR your comment "You can't have an active cell in more than one sheet." answers my question. Please type it as answer and I will flag it as correct – Tms91 Apr 23 '20 at 07:34

1 Answers1

1

The ActiveCell is only the single active cell in whichever sheet is active at the time the code is run. It cannot refer to a cell on an inactive sheet. Another sheet must be activated first.

Of course you can use the Address property of an activecell and refer to the corresponding cell on an inactive sheet.

In general, it is recommended not to use activecell or selection.

SJR
  • 22,986
  • 6
  • 18
  • 26