VBA：如何从查找值的变量返回单元格引用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21787917/
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
VBA: How to return a cell reference from a variable that looksup a value?
提问by user3276971
First of all, I have very little experience with VBA. Here is my problem:
首先，我对 VBA 的经验很少。这是我的问题：
I need to update a certain rate on a daily base in order to perform investments calculations. Right now, I have a button that once clicked brings the day's rate automatically from a website, and I have to copy and paste the rate to its respective date manually. What I would like to do it to just click the button and the rate would automatically be pasted next to its date. The worksheet looks like this:
我需要每天更新一定的费率才能进行投资计算。现在，我有一个按钮，点击后会自动从网站获取当天的费率，我必须手动将费率复制并粘贴到相应的日期。我想要做的只是单击按钮，汇率将自动粘贴在其日期旁边。工作表如下所示：
Update Button
Day's date Day's rate
03/01/2013 6%
Date Rate
02/01/2013 5%
03/01/2013 6%
04/01/2013
The most obvious way to do it would be to use vlookup to look for the the day's date up right and paste it in the correct place at column B, but the problem would be that everytime I update the value, the previous day formula would result in an error. So the best solution would be to use a simple macro that matches the day's date in column A and pastes only the value of the rate next to it.
A good strategy to perform this action with normal worksheet functions would be =CELL("address";INDEX(B:B;MATCH($G$5;A:A;0);)), which would result in the cell reference of the right day's rate in column B. The problem is there isn't the =CELL function in VBA.
最明显的方法是使用 vlookup 查找当天的日期并将其粘贴到 B 列的正确位置，但问题是每次我更新值时，都会产生前一天的公式在一个错误。因此，最好的解决方案是使用一个简单的宏来匹配 A 列中的当天日期，并仅粘贴旁边的汇率值。使用正常工作表函数执行此操作的一个好策略是=CELL("address";INDEX(B:B;MATCH($G$5;A:A;0);))，这将导致 B 列中正确日期的单元格引用。问题是 VBA 中没有 =CELL 函数。
I tried the following script but it didn't work out:
我尝试了以下脚本，但没有成功：
Sub Teste2()
Dim IndexFormula As Range
MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
IndexFormula = WorksheetFunction.Index(Range("B:B"), MatchFormula, 0)
Value = IndexFormula.Address
Range("G5").Select
Selection.Copy
Value.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
What code should I use?
我应该使用什么代码？
Thanks in advance
提前致谢
回答by Dmitry Pavliv
Another option is to use Findfunction:
另一种选择是使用Find功能：
Sub Teste2()
Dim res As Range
Set res = Range("A:A").Find(What:=Range("Today"), Lookat:=xlWhole, MatchCase:=False)
If Not res Is Nothing Then
' res.Offset(, 1) will give you column B (1 to the right from A)
res.Offset(, 1).Value = Range("G5").Value
End If
End Sub
Some notes:
一些注意事项：
- don't use variables with specific names such
Value. One time you'd be confused with unexpected results - instead using
Copy/PasteSpecial xlPasteValuesyou could use justRange("A1").Value = Range("B1").Value - avoid using Select/Active statementsin your code
- 不要使用具有特定名称的变量，例如
Value. 有一次你会对意想不到的结果感到困惑 - 而不是使用
Copy/PasteSpecial xlPasteValues你可以使用Range("A1").Value = Range("B1").Value - 避免在代码中使用 Select/Active 语句
回答by user2140261
You are very close with your try just make a few small changes as below and it will work, No need to use a find as it will be slower:
您非常接近您的尝试，只需进行一些小的更改，如下所示，它会起作用，无需使用查找，因为它会变慢：
Dim MatchFormula As Long
Dim IndexFormula As Range
MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
Set IndexFormula = WorksheetFunction.Index(Range("B:B"), MatchFormula, 0)
IndexFormula.Value = Range("G5").Value
More so if you want it to be even faster you can use:
更重要的是，如果您希望它更快，您可以使用：
Dim MatchFormula As Long
MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
Range("B" & MatchFormula).Value = Range("G5").Value
This works because the Match on the Column returns the row with your value and you can just reference that row in column B to void the need of using index.
这是有效的，因为列上的匹配返回具有您的值的行，您可以只引用列 B 中的该行来避免使用索引的需要。
