VBA Excel Range () with Cell argument

Question

Why the following does not work:

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

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

(I understand that I can just do Cells(1,1).Value = 3 , but I'm just wondering why this is not working.)

I am reading an MSDN entry and showing that the first argument should be A1 style, but something like this works:

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

Totally confused.

Answer 1

If you want to use the Cells property to specify the parameters of a range object (if I remember correctly - I have not used VBA for some time), you must effectively provide two arguments.

So, if you want to reference a range object that has only one cell, you need to write:

 Range(Cells(1, 1), Cells(1, 1)).value = "Hello World" 

Answer 2

When Range used with a single parameter, the parameter is interpreted as the name of the range.

 Range(Cells(1,1)) 

matches using

 Range(Cells(1,1).Value) 

So you get the result, only the value of Cells(1,1) is a valid range address in A1 style

Only when two range parameters are transmitted are they interpreted as range angles.

Answer 3

Instead of referencing a single cell as follows:

 Range(Cells(1,1), Cells(1,1)) 

You can write:

  Range(Cells(1,1).Address) 

Answer 4

For one cell, this is much simpler: use the Cells () function by default:

 Cells(1,1) = "hello world" 

or use the Sheet Cells () function:

 Dim sht as Worksheet Set sht = Sheets("myworksheet") ' or: = Sheets(1) sht.Cells(1,1) = "hello world" 

For the range, you will need to use two parameters, as described in the other answers given here. But the advantage is that you can set a number of fields to a value. And you can work on a sheet that is not “active,” backstage. For instance:

 Const colRand = 4 Const colDiff = 5 Dim sht as Worksheet, rngHi As Range, rngRand As Range, rngDiff As Range Set sht = Sheets("myworksheet") ' or: = Sheets(1) Set rngHi = sht.Range(sht.Cells(1,1), sht.Cells(3,3) rngHi = "hello world" Set rngRand = sht.Range(sht.Cells(1,colRand), sht.Cells(8,colRand) ' column 4, rows 1-8 rngRand = "=RAND()" Set rngDiff = sht.Range(sht.Cells(2,colDiff), sht.Cells(8,colDiff) ' column 5, rows 2-8 ' using FormulaR1C1 in case the sheet isn't set to use that type of formula Set rngDiff.FormulaR1C1="=RC[-1] - R[-1]C[-1]" ' on previous columnn, diff between this row and previous row 

Explanation:

The Cells function gets either:
string parameter - in which you specify the range A1_And_Colon Style
or two cell parameters - the start cell of the range and the end cell.

So, to set the range using "cells", you need to give both cells divided by a comma:

 Range(Cells(1,1), Cells(1,1)) = "hello world" Range(Cells(2,2), Cells(3,4)) = "you cannot square around, but you can round a square" Sheets(1).Cells(5,5) = "=Round(Sqrt(5))" 

Answer 5

I am writing this answer because I am studying VBA, and it took me the best part of three days to find out what is going on here, and the official documentation does not discuss this topic at all. This QA is good, but from my point of view the information is a little scattered.

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

There is a valid object ws ...

You think this will work:

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

Is not. You will receive a 1004 runtime error: the Range method of the _Worksheet object has failed.

The obvious fix described by @Woody_Pride:

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

Unfortunately, the need to do this is absolutely enraging and is not really strictly necessary.

What you really need is @Willby claims, although an explanation of why this is actually is in @chris_neilsen's answer:

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

This will also work as @pashute suggests (which is mistaken in most parts of its explanation):

ws.Cells(i,j)

Thanks to everyone who contributed on this page; I feel that I now finally have the whole picture.

Answer 6

When using cells, you need to formulate Object.cells, for example Application.cells (2,2) or activeWorksheet.cells