VBA Excel Range() with Cell argument

Question

Why does the following not work:

Range(Cells(1,1)).Value = 3

Cells(1,1) should essentially be the same thing as using A1 right?

(I realize that I could just do Cells(1,1).Value = 3, but I'm just curious as to why it doesn't work.)

I read the MSDN entry and it shows that the first argument must be A1 style, yet something like this does work:

Range(Cells(1,1), Cells(2,3)).Value = 2

Totally confused.

Answer 1

I'm writing this answer because I'm learning VBA and it took me the better part of three days to figure out what was happening here, and the official documentation does not discuss this topic at all. This QA is good but the information is a bit scattered, from my perspective today.

Here's what I know about using the Cells() property inside a Range() object to reference a single-cell range. Which I need to do all the time!

Given a valid ws object...

You think this will work:

ws.Range(ws.Cells(i,j))

It doesn't. You'll get Run-time error '1004': Method 'Range' of object'_Worksheet' failed.

The obvious fix, as described by @Woody_Pride is:

ws.Range(ws.Cells(i,j), ws.Cells(i,j))

Unfortunately, having to do this is absolutely infuriating, and is not actually strictly necessary.

What you actually need is, as asserted by @Willby, although the explanation as to why this is the case is actually in the answer by @chris_neilsen:

ws.Range(ws.Cells(i,j).Address)

This will also work, as suggested by @pashute (who is wrong in most parts of his explanation):

ws.Cells(i,j)

Thank you to everyone who contributed on this page; I feel like I now, finally, have the entire picture.