Excel VBA - 将变量的值粘贴到单元格中

Excel VBA - Paste a variable's value into a cell

提问by pez

I have a Forloop which will find a cell with a certain value in it (e.g. the word "Item"). Once it finds this cell, I want to paste a value into the row below it, in column I.

我有一个For循环，它将找到一个具有特定值的单元格（例如单词“Item”）。一旦找到这个单元格，我想将一个值粘贴到它下面的行中，在第一列。

For example, if the Forloop finds "Item" in cell A1, I want to paste the value of a variable into cell I2.

例如，如果For循环在单元格 A1 中找到“Item”，我想将变量的值粘贴到单元格 I2 中。

I'd like to be able to do this with both a Longvariable and a Stringvariable.

我希望能够同时使用Long变量和String变量来做到这一点。

Right now I'm trying something like:

现在我正在尝试类似的东西：

Cells(i.Offset(1, 0), 9).Value = myLongVariable

or

或者

Cells(i.Offset(1, 0), 9).Text = myStringVariable

and other variations, but I keep getting an overflow error at this line.

和其他变体，但我在这一行不断收到溢出错误。

I've tried this: Range("I" & i.Offset(1, 0)).Value = myLongVariableand slight variations, but I get an error that says Run-time error 1004: Method Range of object _Worksheet failed

我试过这个：Range("I" & i.Offset(1, 0)).Value = myLongVariable和轻微的变化，但我收到一个错误说Run-time error 1004: Method Range of object _Worksheet failed

Or workBook.Sheets("Sheet1").Range("I" & i.Offset(1, 0)).Value = myLongVariablebut that gives Run-time error 1004: Application-defined or object-defined error. Not sure what that refers to since I've defined workBookand iand myLongVariable.

或者，workBook.Sheets("Sheet1").Range("I" & i.Offset(1, 0)).Value = myLongVariable但这给Run-time error 1004: Application-defined or object-defined error. 不确定这是指什么，因为我已经定义了workBookandi和myLongVariable。

As a slightly different approach, I've tried pasting the variable data into the next blank cell of the row, which happens to be the cell in column I. Here's the code I used for that: 'i.Offset(1, 0).End(xlToRight).Offset(0, 1).Value = myLongVariable. It works great, except in this scenario: cells A1:C1 are blank, D1:H1 have values. It should then paste the value into I1, but instead it thinks D1 is the end and posts it there. Any ideas on that?

作为一个稍微不同的方法，我已经试过了可变数据粘贴到该行，这恰好是在一列这里的单元格的下一个空白单元格是我用的代码：'i.Offset(1, 0).End(xlToRight).Offset(0, 1).Value = myLongVariable。它工作得很好，除了在这种情况下：单元格 A1:C1 是空白的，D1:H1 有值。然后它应该将该值粘贴到 I1 中，但它认为 D1 是结束并将其发布在那里。对此有何想法？

Thanks!

谢谢！

回答by Steven Martin

Its wrong to use offset here, because you are not referencing a cell,

在这里使用偏移量是错误的，因为您没有引用单元格，

i is just a number, so you cant offset it

我只是一个数字，所以你不能抵消它

Cells(i.Offset(1, 0), 9).Value = myLongVariable
Cells(i.Offset(1, 0), 9).Text = myStringVariable

should be

应该

Cells(i + 1, 9).Value = myLongVariable
Cells(i + 1, 9).Value = myStringVariable

回答by Dirk Reichel

Just to be different from the other answer you also can use this:

只是为了与其他答案不同，你也可以使用这个：

i.Offset(1).EntireRow.Cells(9).Value = myLongVariable
i.Offset(1).EntireRow.Cells(9).Value = myStringVariable

Or to ensure to use the correct column:

或者确保使用正确的列：

i.Offset(1).EntireRow.Columns("I").Value = myWhateverVariable

still, like said in my comment Cells(i.Row + 1, 9)also is possible or Cells(i.Row + 1, "I");)

仍然，就像我在评论中所说的那样Cells(i.Row + 1, 9)也是可能的，或者Cells(i.Row + 1, "I");)

回答by arcadeprecinct

This assumes that iis a range because you had that in another question: i.Offset(1,0)returns a Range object, not a number. The way you use it in your code looks like you are trying to use it as the number of a row. To get the Row of a range, just use the .Rowproperty:

这假设这i是一个范围，因为您在另一个问题中遇到了这个问题：i.Offset(1,0)返回一个 Range 对象，而不是一个数字。您在代码中使用它的方式看起来像是您试图将其用作行数。要获取范围的行，只需使用.Row属性：

i.Offset(1,0).Row
i.Row + 1 'same thing 

Then

然后

Cells(i.Offset(1, 0), 9).Value = myLongVariable

should be

应该

Cells(i.Row+1, 9).Value = myLongVariable

---

---

Next thing:

下一件事：

 Cells(i.Offset(1, 0), 9).Text = myStringVariable

This is the same problem with Offsetbut also the .Textproperty. The .Textproperty is read only and returns the string that is displayed on screen. It differs from the value for example if the cell is too small, it becomes #####. Use the .Valueproperty for strings as well as numbers.

这与Offset但也是.Text属性相同的问题。该.Text属性是只读的，并返回显示在屏幕上的字符串。它与值不同，例如，如果单元格太小，则变为#####。将该.Value属性用于字符串和数字。

---

---

Now for the last thing:

现在是最后一件事：

Somecell.End(xlToRight)

End(xlToRight)looks for the end of the region, which means that if the cell is empty, it looks for the end of the empty region, which is the next cell that contains something or the end of the sheet. If the cell is not empty, it looks for the first empty cell. What you want is to search from the right for the first non-empty cell:

End(xlToRight)查找区域的结尾，这意味着如果单元格为空，则查找空区域的结尾，即下一个包含某些内容的单元格或工作表的结尾。如果单元格不为空，则查找第一个空单元格。您想要的是从右侧搜索第一个非空单元格：

Cells(Somecell.Row,Columns.Count).End(xlToLeft) 'this is the last nonempty cell in the Row of Somecell

you have to move one column to the right to get the first empty cell, for example using Offset.

您必须向右移动一列才能获得第一个空单元格，例如使用Offset.