如何通过VBA引用单元格 [英] how to reference a cell through vba

问题描述

我刚刚编写了一些代码，这些代码从一个工作簿中获取值并将其粘贴到另一工作簿中.当前它粘贴为值.这是我的代码的某些部分:

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)

然后激活另一本书，

Cells(j - 5, 4) = Bonds_IE

我希望它显示为从中提取信息的参考.例如:K:\ etc \ etc \ etc [etc.xlsx]等！$ A $ 1

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

我觉得这应该超级简单，但我不确定.请帮忙 :(谢谢！

I feel like this should be super simple but I am not sure.. Please help :( Thanks!!

推荐答案

VBA代码隐式地做了很多事情.如果我们将所有内容都明确化，问题就会变得更加清晰:

VBA code does a lot of things implicitly. If we make everything explicit, the problem becomes clearer:

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)

注意:

• 隐式字符串转换，使用 CStr 进行了显式的转换.如果单元格包含错误值，这些将爆炸并出现 type mismatch 错误.在使用该值进行任何处理之前，您可能需要验证 IsError 是否返回 False .

• 隐式 ActiveSheet 引用.如果将这些 用于不符合有效工作表的合格 Range 调用，则运行时错误1004，例如:

• Implicit string conversions, made explicit with CStr. These will blow up with a type mismatch error if the cell contains an error value. You may want to verify whether IsError returns False given a cell's value, before doing anything with that value.

• Implicit ActiveSheet references. These will blow up with run-time error 1004 if you use them against a qualified Range call that isn't referring to the active sheet, e.g.:

Sheet12.Range(Cells(i, 1), Cells(i + 1, 1)) = 42 'Sheet12 isn't active

隐式默认成员调用. Cells 返回一个 Range 对象；其默认成员指向其 Value .这就是为什么您可以执行 Cells(j-5，4)= foo 并且仍然具有可编译代码的原因.明确显示成员调用有助于更好地理解阅读代码时真正发生的事情.

Implicit default member calls. Cells returns a Range object; its default member points to its Value. That's why you can do Cells(j - 5, 4) = foo and still have compilable code. Making member calls explicit helps better understand what's really going on when you read the code.

如前所述， Range.Value 获取/设置范围的值.您想要的是它的 .Formula .

As already mentioned, Range.Value gets/sets the range's value. What you want is its .Formula.

这篇关于如何通过VBA引用单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！