在宏或 VBA 中使用匹配和地址函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8398930/
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
Using Match and Address functions within Macro or VBA
提问by Tony Sanders
I have two worksheets, I want to use a value in sheet to_approveto lookup against column Ain sheet submitted, then identify the cell reference so I can paste a value in the cell adjacent (column B).
我有两个工作表，我想使用的值，片材to_approve来查找针对column A在片材submitted，然后确定单元格引用，所以我可以粘贴在小区相邻的一个值（column B）。
I have used the following to identify the cell reference, but I don't know how to use it in VBA code.
我已经使用以下内容来识别单元格引用，但我不知道如何在 VBA 代码中使用它。
=ADDRESS(MATCH(To_Approve!D19,Submitted!A:A,0),1,4,1,"submitted")
回答by brettdj
While many functions can be used in VBA using Application.WorksheetFunction.FunctionNameADDRESS is not one of these (MATCH is)
虽然许多功能可以在 VBA 中使用Application.WorksheetFunction.FunctionNameADDRESS 不是其中之一（MATCH 是）
But even it it was available I would still use a Find methodas below as it:
但即使它可用，我仍然会使用如下的Find 方法：
- gives you the ability to match whole or part strings, case sensitive or not
- returns a range object to work with if the value is found
- readily handles a no match
- you can control the point in the range being search as to where the search starts
- multiple matches can be returned with
FindNext
- 使您能够匹配整个或部分字符串，区分大小写
- 如果找到该值，则返回要使用的范围对象
- 轻松应对不匹配
- 您可以控制搜索范围内的点，以了解搜索的开始位置
- 可以返回多个匹配项
FindNext
something like
就像是
Sub GetCell()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("submitted")
Set rng1 = ws.Columns("A").Find(Sheets("To_Approve").[d19], , xlValues, xlWhole)
If Not rng1 Is Nothing Then
MsgBox rng1.Address & " in sheet " & ws.Name
Else
MsgBox "not found", vbCritical
End If
End Sub
回答by Rachel Hettinger
This example should give you an idea of how to find a corresponding value on another sheet and place a second value in the the column to the left. When using VBA, it is not necessary to select cells and then paste; you can directly enter a value into a range (cell) object.
此示例应该让您了解如何在另一张纸上找到相应的值并将第二个值放置在左侧的列中。使用VBA时，不需要先选中单元格再粘贴；您可以直接在范围（单元格）对象中输入值。
Sub TransferValue()
Dim rngSearch As Range
Dim rngFind As Range
Dim dValue As Double
' initialization
Set rngSearch = Worksheets("to_approve").Range("D19")
dValue = Date
' find the match & insert value
Set rngFind = Worksheets("submitted").Columns(1).Find(What:=rngSearch.Value)
If Not rngFind Is Nothing Then
rngFind.Offset(ColumnOffset:=1).Value = dValue
End If
End Sub
(Note: dValue is a placeholder for whatever value you want to enter.)
（注意：dValue 是您要输入的任何值的占位符。）
