9

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.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
user943870
  • 303
  • 2
  • 3
  • 9

7 Answers7

13

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

Range(Cells(1,1))

is the same as using

Range(Cells(1,1).Value)

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

Only when passed two range parameters are they interpreted as the corners of a range.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Good one, but could it be more complete with the cells(1,1).Address example? – LuizAngioletti Aug 09 '15 at 19:27
  • 1
    Range(Cells(1,1).Value) throws an error on my machine. Range(Cells(1,1).Address) works though. – user3032689 Feb 13 '16 at 15:44
  • @user3032689 'Cells(1,1).Value' returns the value in the cell. (If that cell value is not an A1 style address, 'Range(Cells(1,1).Value)' will throw an error). Otherwise, Cells(1,1).Address returns the string "$A$1" which is a valid A1 style address for Range. – hstay Dec 20 '16 at 21:25
6

When you want to use the Cells property to specify the parameters of the range object (if I remember rightly - I've not been using VBA for some time), then you have to effectively supply two arguments.

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

Range(Cells(1, 1), Cells(1, 1)).value = "Hello World"
Woody Pride
  • 13,539
  • 9
  • 48
  • 62
  • It's just confusing because it contradicts their own documentation, as far as I can tel(says you have to use A1 style as the first argument). I guess it's just one of those odd design decisions you have to live with. – user943870 Jul 14 '13 at 03:32
  • Not exactly, if you scroll down the documentation you will see that there are different conditions if you are using the Cells property... In that section it does not in fact mention the A1 style (obviously as it is concerned with the Cells property). – Woody Pride Jul 14 '13 at 03:34
  • http://msdn.microsoft.com/en-us/library/office/ff836512.aspx is where it specifies the first argument requirement. I'm new to VBA, so am I looking at the wrong entry? – user943870 Jul 14 '13 at 03:39
  • 1
    http://msdn.microsoft.com/en-us/library/office/aa139976(v=office.10).aspx might be more useful, it explains about using the Cells property in a range object. – Woody Pride Jul 14 '13 at 03:48
  • 1
    Cells(1,1) = "hello world" ' works the same, shorter code – pashute Dec 20 '16 at 20:31
  • @WoodyPride that documentation page is incomplete at best, misleading at worst, because it really does make it look like `Range(Cells(1,1)).Value = 3` should work, but it does not seem to. There are no examples in the official documentation that I can find that discusses using the cells property inside a range object to define a single cell range vs a multi cell range, and in its absence every example seems to imply that it should work. – Alex M Sep 20 '19 at 18:48
3

Instead of referring to a single cell like this:

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

You can write:

 Range(Cells(1,1).Address)
Willby
  • 39
  • 1
2

For a single cell its much easier: Use the default Cells() function:

Cells(1,1) = "hello world"

or use a Sheet's Cells() function: 

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

For a range you'll have to use two params, as explained in the other answers given here. But the advantage is that you can set a whole range of fields to a value. And you can work on a sheet that isn't the 'Active one', behind the scenes. For example: 

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 receives either:
a string parameter - in which you specify the A1_And_Colon Style range
or two Cell parameters - the beginning cell of the range and the end cell.

So to set the range with '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))"
pashute
  • 3,965
  • 3
  • 38
  • 65
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.

Alex M
  • 134
  • 8
  • `ws.Range(Cells(i,j).Address)` is a really bad way of referencing a single cell - it's got an embedded implicit reference to the Active Sheet which will error if `ws` is not active. And `ws.Cells(i, j)` already references the required range, so the whole thing is pointless – chris neilsen Sep 20 '19 at 21:02
  • @chrisneilsen I edited to update `ws.Range(Cells(i,j).Address)` to `ws.Range(ws.Cells(i,j).Address)`... thanks for the reminder. As to ws.Cells(i, j) I'm just trying to figure out how to reference a single cell, and I kept getting errors when I tried to write ws.Range(ws.Cells(i,j)) and I couldn't figure out why, since the documentation SEEMS to indicate that that should work. – Alex M Sep 20 '19 at 21:08
  • As for _who provided an explanation but no answer_ the OP asked _Why does the following not work_ which is exactly what I answered. The OP also said they knew how to do it correctly, so no need to repeat that – chris neilsen Sep 20 '19 at 21:10
  • For example, I just want to be able to copy a record from one place to another; `wsDestination.Range("A1") = wsSource.Range("A1")` works, but I want to use a variable in the cell reference. The documentation says you can use a `Cells(i, j)` style reference to replace a R1C1 style reference, but in this context you can't. – Alex M Sep 20 '19 at 21:11
  • Really, don't do that double reference thing - just use ws.Cells(...). – chris neilsen Sep 20 '19 at 21:12
  • re explanation vs. answer, fair point - I was projecting my own desires for this QA onto the original asker. I've edited that out of my answer. – Alex M Sep 20 '19 at 21:12
  • 1
    Suggest you ask a Q with all the details of what you want, what you tried, how it failed. I gaurantee you'll get an answer that doesn't involve Range(Cells(...).Address) – chris neilsen Sep 20 '19 at 21:18
  • @chrisneilsen _Really, don't do that double reference thing - just use ws.Cells(...)._ this exchange has been very helpful, thank you!! – Alex M Sep 20 '19 at 21:18
0

I know sometimes you need a range for other properties other than value. What i would do is make a function to help you:

Public Function cellRange(ws As Worksheet, rowNum As Integer, colNum As Integer) As Range
    Set cellRange = ws.Range(ws.Cells(rowNum, colNum), ws.Cells(rowNum, colNum))
End Function

This way you can make cleaner code:

Set ws = ActiveWorkbook.Sheets("Sheet1")
cellRange(ws, 1, 3).Interior.Color = cellRange(ws, 1, 8).Interior.Color
Mark Walsh
  • 985
  • 1
  • 7
  • 12
-3

When using "cells", it is required to formulate Object.cells , e.g. Application.cells(2,2) or activeWorksheet.cells

BerendVink
  • 1