Using Match and Address functions within Macro or VBA

Question

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).

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")

Answer 1

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

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

• 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

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