CODEWITHSUNDEEP
vbaexcel
user943870
user943870

Reputation: 303

VBA Excel Range() with Cell argument

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.

Upvotes: 9

Views: 40809

Answers (7)

Mark Walsh
Mark Walsh

Reputation: 1035

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

Upvotes: 0

Alex M
Alex M

Reputation: 134

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.

Upvotes: 1

pashute
pashute

Reputation: 4053

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))"

Upvotes: 2

Willby
Willby

Reputation: 39

Instead of referring to a single cell like this:

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

You can write:

 Range(Cells(1,1).Address)

Upvotes: 3

BerendVink
BerendVink

Reputation: 1

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

Upvotes: -3

chris neilsen
chris neilsen

Reputation: 53126

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.

Upvotes: 13

Woody Pride
Woody Pride

Reputation: 13955

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"

Upvotes: 6

Related Questions