What's the equivalent of the following Excel code in VBA?
=MATCH(A1,Ranged_Name,0)
where Ranged_Name is a ranged name on the workbook level.
-
You may want to read about worksheet functionscybernetic.nomad– cybernetic.nomad2018-11-20 18:33:03 +00:00Commented Nov 20, 2018 at 18:33
Add a comment
|
3 Answers
To add one more:
Dim r
With Activesheet
r = Application.Match(.Range("A1").Value, Range("Ranged_Name"), 0)
End with
If Not IsError(r) Then
'got a match
End If
Comments
In both a worksheet cell and VBA:
Sub dural()
MsgBox Evaluate("Match(A1,Range_Name,0)")
End Sub
EDIT#1:
Based on the Comments, a better alternative:
Sub dural()
Dim r1 As Range, r2 As Range, v As Variant, wf As WorksheetFunction
Set r1 = Sheets("Sheet1").Range("A1")
Set r2 = Range("Range_Name")
Set wf = Application.WorksheetFunction
v = wf.Match(r1, r2, 0)
MsgBox v
End Sub
and to use Find():
Sub larud()
Dim r1 As Range, r2 As Range, v As Variant
Set r1 = Sheets("Sheet1").Range("A1")
Set r2 = Range("Range_Name")
v = r2.Find(What:=r1.Value, After:=r2(1)).Row
MsgBox v
End Sub
4 Comments
Tim Williams
Might be an improvment to use the
Worksheet.Evaluate method, rather than the Application version? Otherwise, the result will vary according to which sheet is active.
Garry W
I used Evaluate("Match('Sheet1'!A1,Range_Name,0)") and that also worked. :)
Gary's Student
@GarryWang See my EDIT#1
Garry W
@Gary'sStudent I surely feel like the student here. Thank you for the help!
You can either use Range().Find or Evaluate() your Match, if the sheet is the ActiveSheet.
