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.
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.
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"
Instead of referring to a single cell like this:
Range(Cells(1,1), Cells(1,1))
You can write:
Range(Cells(1,1).Address)
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))"
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.
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
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.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.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
When using "cells", it is required to formulate Object.cells , e.g. Application.cells(2,2) or activeWorksheet.cells
