Match Function in VBA...

fadee2

Active Member
Joined
Nov 7, 2020
Messages
404
Office Version
  1. 2021
Platform
  1. Windows
hi all,

I am trying to match a value in a range of cells, using vba.
my worksheet looks like this
Daily_Wages_Computations.xlsm
ABCDJ
1Name01/12/2001/01/2101/12/20
2A300001000
3B21500200
4C12000500
5D265001000
6E220006000
7F220005000
8G220006000
Sheet4


and this is my code

VBA Code:
Sub matchVal()
Dim lookmon As Range

Set look_mon = Sheets("sheet4").Range("b1:f1")     'lookup range
lookval = Range("b1")                'lookup value

Sheets("sheet4").Range("f20") = WorksheetFunction.Match(lookval, look_mon, 0)

End Sub
the motive is to sum values in column if value in b1 or c1 matches the vakue in j1.
the code returns unable to get match property error, please refer to below image.
1608551522060.png

I am unable to figure out the missing link in the above code.
I can use index to return the value, which is working fine also, but I am trying to figure out what has gone wrong with Match function. any help is appreciated.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try change:

Sheets("sheet4").Range("f20") = WorksheetFunction.Match(lookval, look_mon, 0)

to:

Sheets("sheet4").Range("f20") = Application.Match(lookval, look_mon, 0)
 
Upvote 0
Try
VBA Code:
WorksheetFunction.match(CLng(lookval), look_mon, 0)
but this will just return 1 not the actual value
 
Upvote 0
Solution
Try change:

Sheets("sheet4").Range("f20") = WorksheetFunction.Match(lookval, look_mon, 0)

to:

Sheets("sheet4").Range("f20") = Application.Match(lookval, look_mon, 0)
thiss returns error 2042
 
Upvote 0
Not an expert but you have 'DIM lookmon', but your code shows it as 'look_mon' ??
 
Upvote 0
Try
VBA Code:
WorksheetFunction.match(CLng(lookval), look_mon, 0)
but this will just return 1 not the actual value
Thanks @Fluff, works like a charm, as I only needed it to return the column number.
Below is the solution using your piece of code.

VBA Code:
Sub matchVal2()
Dim look_mon As Range
lr = Sheets("sheet4").Cells(Rows.Count, 2).End(xlUp).Row

Set look_mon = Sheets("sheet4").Range("a1:f1")     'lookup range
lookval = Range("c1")                'lookup value

s = WorksheetFunction.Match(CLng(lookval), look_mon, 0)

Sheets("sheet4").Range("j2") = WorksheetFunction.Sum(Range(Cells(2, s), Cells(lr, s)))

End Sub

furthermore, as i mentioned before, I can use Index to return the results.
below is my code using index.

VBA Code:
Sub matchVal()
Dim irng As Range, mrng As Range

lr = Sheets("sheet4").Cells(Rows.Count, 2).End(xlUp).Row
lc = Sheets("Sheet4").Cells(1, Columns.Count).End(xlToLeft).Column

Set irng = Sheets("sheet4").Range(Cells(2, 2), Cells(lr, 3))
Set mrng = Sheets("sheet4").Range(Cells(1, 2), Cells(1, 3))

Sheets("sheet4").Range("j2") = WorksheetFunction.Sum(WorksheetFunction.Index(irng, 0, _
    WorksheetFunction.Match(Range("j1"), mrng, 0)))

End Sub
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,215,415
Messages
6,124,768
Members
449,187
Latest member
hermansoa

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