vba 如何通过vba引用单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44031495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to reference a cell through vba
提问by Toby Kim
I just wrote some codes which pick up values from one workbook,worksheet and paste them in another workbook, worksheet. Currently it pastes in as values. Here are some parts of my code:
我只是写了一些代码，它们从一个工作簿、工作表中获取值并将它们粘贴到另一个工作簿、工作表中。目前它作为值粘贴。这是我的代码的一些部分：
Do Until Cells(i, 9) = "Net Income"
If Cells(i, 9) = "BONDS INTEREST EARNED" Then
Bonds_IE = Cells(i + 1, 9)
and then with another book activated,
然后激活另一本书，
Cells(j - 5, 4) = Bonds_IE
I want this to show up as a reference to where it pulled the information from. for Ex: K:\etc\etc\etc[etc.xlsx] etc!$A$1
我希望它显示为从哪里提取信息的参考。例如：K:\etc\etc\etc[etc.xlsx] etc!$A$1
I feel like this should be super simple but I am not sure.. Please help :( Thanks!!
我觉得这应该非常简单，但我不确定..请帮忙:(谢谢！！
回答by Mathieu Guindon
VBA code does a lot of things implicitly. If we make everything explicit, the problem becomes clearer:
VBA 代码隐式地做了很多事情。如果我们把一切都说清楚，问题就变得更清楚了：
Do Until CStr(ActiveSheet.Cells(i, 9).Value) = "Net Income"
If CStr(ActiveSheet.Cells(i, 9).Value) = "BONDS INTEREST EARNED" Then
Bonds_IE = CStr(ActiveSheet.Cells(i + 1, 9).Value)
Note:
笔记：
- Implicit string conversions, made explicit with
CStr. These willblow up with a type mismatcherror if the cell contains an error value. You may want to verify whetherIsErrorreturnsFalsegiven a cell's value, before doing anything with that value. Implicit
ActiveSheetreferences. These willblow up with run-time error 1004 if you use them against a qualifiedRangecall that isn't referring to the active sheet, e.g.:Sheet12.Range(Cells(i, 1), Cells(i + 1, 1)) = 42 'Sheet12 isn't activeImplicit default membercalls.
Cellsreturns aRangeobject; its default member points to itsValue. That's why you can doCells(j - 5, 4) = fooand still have compilable code. Making member calls explicit helps better understand what's reallygoing on when you read the code.
- 隐式字符串转换，使用
CStr. 如果单元格包含错误值，这些将因类型不匹配错误而爆炸。在对该值执行任何操作之前，您可能想要验证是否IsError返回False给定单元格的值。 隐式
ActiveSheet引用。这些将炸毁了运行时错误1004，如果你使用它们对一个合格的Range说是不是指到活动工作表，如通话：Sheet12.Range(Cells(i, 1), Cells(i + 1, 1)) = 42 'Sheet12 isn't active隐式默认成员调用。
Cells返回一个Range对象；它的默认成员指向它的Value. 这就是为什么你可以做Cells(j - 5, 4) = foo并且仍然拥有可编译的代码。使成员调用显式有助于在阅读代码时更好地了解实际发生的情况。
As already mentioned, Range.Valuegets/sets the range's value. What you want is its .Formula.
如前所述，Range.Value获取/设置范围的值。你想要的是它的.Formula.
回答by YowE3K
Try using the source cell's Addressto create a Formula:
尝试使用源单元格Address创建一个Formula：
Dim Bonds_IE As String
Do Until Cells(i, 9) = "Net Income"
If Cells(i, 9) = "BONDS INTEREST EARNED" Then
Bonds_IE = "=" & Cells(i + 1, 9).Address(RowAbsolute:=True, _
ColumnAbsolute:=True, _
External:=True)
'...
'...
Cells(j - 5, 4).Formula = Bonds_IE
One trick with this though ... Address(External:=True)will not include the pathto the original workbook, so the resultant string needs to be applied as a Formulato the other cell beforethe original workbook is closed. That way, when the original workbook is closed, Excel will automatically update the formula to include the path as well.
但是，一个技巧......Address(External:=True)不会包含原始工作簿的路径，因此在关闭原始工作簿之前，需要将生成的字符串作为 a 应用于Formula另一个单元格。这样，当原始工作簿关闭时，Excel 将自动更新公式以包含路径。
回答by Kyle
You just need to reference the .Formulaof the cell, instead of the default, which is .Value.
您只需要引用.Formula单元格的 ，而不是默认的.Value。
Do Until Cells(i, 9) = "Net Income"
If Cells(i, 9) = "BONDS INTEREST EARNED" Then
Bonds_IE = Cells(i + 1, 9).Formula
Cells(j - 5, 4).Formula = Bonds_IE
