带单元格参数的VBA Excel Range()

Why does the following not work:

为什么以下的方法不起作用:

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

范围(细胞(1,1))。值= 3

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

单元格(1,1)本质上应该和使用A1是一样的，对吧?

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

(我意识到我可以做细胞(1,1)。Value = 3，但我很好奇为什么它不管用。

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

我读了MSDN条目，它表明第一个参数必须是A1样式，但是类似这样的东西确实有用:

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

范围(细胞(1,1),(2、3)。值= 2

Totally confused.

完全搞糊涂了。

5 个解决方案

#1

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.

当您希望使用单元格属性来指定range对象的参数时(如果我没记错的话——我已经有一段时间没有使用VBA了)，那么您必须有效地提供两个参数。

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"

#2

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

当Range与单个参数一起使用时，该参数将被解释为一个Range名称。

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

因此，您将得到的结果只是单元格(1,1)的值是A1样式的有效范围地址

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

只有当传递了两个范围参数时，它们才被解释为一个范围的角。

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

#4

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

对于单个单元格，它更容易:使用默认的单元格()函数:

Cells(1,1) = "hello world"

or use a Sheet's Cells() function:

或者使用表单的cell()函数:

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.

单元格函数接收以下两个参数:字符串参数——其中指定a1_and_冒号样式范围或两个单元格参数——范围的起始单元格和结束单元格。

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

#5

-2

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

在使用“单元”时，需要对对象进行描述。单元格，例如application .cell(2,2)或activeWorksheet.cells

#1