1

I am trying to refine a cell address in Excel VBA. I have an address written:

ActiveCell.Offset(, -2).Value = ""

An I need to place a sheet in that too that's:

Worksheets("Machine Format")

Every combination that I could think of returned an error that it's not correct.

Could someone, please, help me with this code?

Eduards
  • 68
  • 2
  • 20
  • 1
    Also, I am sure whatever you are trying to achieve can be done without using `ActiveCell`. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout May 21 '21 at 06:13
  • I need activecell because that is a dynamic thing in my project which may move left and right depending on various complex systems – Eduards May 21 '21 at 06:15
  • Doesn't matter. `ActiveCell` will always refer to activesheet. You can use a range object which would not need a sheet to be active. If you can share more details, I would be more than happy to help. – Siddharth Rout May 21 '21 at 06:16
  • Range as in (A2:C6) for example? – Eduards May 21 '21 at 06:18
  • When I said share more details, I was expecting a small description as to what exactly are you trying to achieve? – Siddharth Rout May 21 '21 at 06:19
  • If you want to work with that range in that sheet only then you can use `Worksheets("Machine Format").Range(A2:C6)` But like I said, more details please :) – Siddharth Rout May 21 '21 at 06:20
  • I know, but I suspect taht it won't be what I need. However... basically I have a worksheet and userform that is able to add columns and fill them from userform. Also you can move through columns selecting the one you need and editing it from userform. There's also 3 worksheets and many userforms taht scatter values through those 3 worksheets and this question occurred because in such code that I have - it only works if the worksheet in question IS the selected/active one – Eduards May 21 '21 at 06:22
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/232676/discussion-between-siddharth-rout-and-eduards). – Siddharth Rout May 21 '21 at 06:25

2 Answers2

0

Under normal cicumstance, i.e your active cell is C7, by execution of your code, it will update the value in A7 enter image description here

However, it is not recommended to use offset -2, because when your active cell is B7 or A7 it will return error due to it is beyond the border

enter image description here

How to solve, use if function if you still preferred offset, such as

If ActiveCell.Column > 2 Then
    ActiveCell.Offset(0, -2).Value = 100
End

So that, it wont perform offset -2 unless it is on col C or above

Kin Siang
  • 2,644
  • 2
  • 4
  • 8
  • The problem is not with finding the cell but rather with the worksheet as my code works only if the needed sheet is active... but I need it to find it in the workbook regardless of that :) In my case I always need to write Worksheets("Machine Format").Select before that activecell code just so it's the active sheet and I'm trying to combine those two things – Eduards May 21 '21 at 06:13
  • `In my case I always need to write Worksheets("Machine Format").Select before that activecell code` @Eduards If you patiently go though the link that I gave below the question, you will not have that problem :) – Siddharth Rout May 21 '21 at 06:22
  • It is not that tough to solve it, when you write `Worksheets("Machine Format").Select`, then you can write another code such as `Range("C1".select` then you will have no such issue in future – Kin Siang May 21 '21 at 06:27
0
Sheets("Machine Format").Range(ActiveCell.Offset(, -2).Address).Value = ""
Eduards
  • 68
  • 2
  • 20