在宏或VBA中使用匹配和地址功能 [英] Using Match and Address functions within Macro or VBA

问题描述

我有两个工作表，我想使用工作表to_approve中的值来查找工作表submitted中的column A，然后标识单元格引用，以便将值粘贴到相邻的单元格中(column B ).

I have two worksheets, I want to use a value in sheet to_approve to lookup against column A in sheet submitted, then identify the cell reference so I can paste a value in the cell adjacent (column B).

我已经使用以下内容来标识单元格引用，但是我不知道如何在VBA代码中使用它.

I have used the following to identify the cell reference, but I don't know how to use it in VBA code.

=ADDRESS(MATCH(To_Approve!D19,Submitted!A:A,0),1,4,1,"submitted")

推荐答案

使用Application.WorksheetFunction.FunctionName在VBA中可以使用许多功能，而ADDRESS不是其中之一(MATCH是)

While many functions can be used in VBA using Application.WorksheetFunction.FunctionName ADDRESS is not one of these (MATCH is)

但是即使可以使用，我仍然会使用 Find方法，如下所示:

But even it it was available I would still use a Find method as below as it:

• 使您能够匹配全部或部分字符串(区分大小写)
• 如果找到该值，则返回要使用的范围对象
• 随时处理不匹配的情况
• 您可以控制搜索范围内的点，以开始搜索
• 可以使用FindNext
返回多个匹配项

• 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

类似

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

这篇关于在宏或VBA中使用匹配和地址功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！