VBA MATCH function

tackage

New Member
Joined
Oct 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello and thanks so much for any help that can be offered.

I am attempting to use the MATCH function to pass a cell in one worksheet as the lookup_value and a column in a second worksheet as the lookup_array. I am having trouble with the syntax and am looking for recommendations.

This is what I have:

Dim rowNumber As Integer
Dim i As Integer ' This is a counter through a loop
rowNumber = "=MATCH(Worksheets(2).Cells(i, "B").Value,Worksheets(3).Range("B"),0)"

I've also tried
rowNumber = Application.WorksheetFunction.Match(Worksheets(2).Cells(i, "B").Value,Worksheets(3).Range("B"),0)

and neither is working. I'm currently teaching myself VBA for Excel and I am definately struggling with the syntax.

Thanks so much!
-Tom
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Rather than using the worksheet function there is a much better way of doing this task in VBAS at this using the dictionary object. You have only posted part of your code so I am guessing about the rest of it. This code does the equivalent of an index match and will much much faster than trying to use index and match in vba:
VBA Code:
Sub dictionarymatch()
' this shows the use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets(3)
      Ary = .Range("B2", .Range("B" & Rows.Count).End(xlUp).Offset(, 6)).Value2
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 7)
   Next i
   With Worksheets(2)
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = Dic(Cl.Value) 'this matches the value given by tyhe index C1.value in the dictionary
      Next Cl
   End With
End Sub
 
Upvote 0
Rather than using the worksheet function there is a much better way of doing this task in VBAS at this using the dictionary object. You have only posted part of your code so I am guessing about the rest of it. This code does the equivalent of an index match and will much much faster than trying to use index and match in vba:
VBA Code:
Sub dictionarymatch()
' this shows the use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets(3)
      Ary = .Range("B2", .Range("B" & Rows.Count).End(xlUp).Offset(, 6)).Value2
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 7)
   Next i
   With Worksheets(2)
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = Dic(Cl.Value) 'this matches the value given by tyhe index C1.value in the dictionary
      Next Cl
   End With
End Sub

Thanks so much - I will play with this and let you know.
I truly appreciate it!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top