Match Function in VBA...

[fadee2]

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
	A	B	C	D						J
1	Name	01/12/20	01/01/21							01/12/20
2	A	30000	1000
3	B	21500	200
4	C	12000	500
5	D	26500	1000
6	E	22000	6000
7	F	22000	5000
8	G	22000	6000
Sheet4

and this is my 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.

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.

 

[fadee2]

A question, in case if the lookup value is created

lookval = DateValue("1/" & Month(minday) & "/" & Year(minday))

where minday is calculated like

minday = Application.WorksheetFunction.Min(ActiveSheet.Range("fulldates"))

what changes should i make to the code below??

s = WorksheetFunction.Match(CLng(lookval), look_mon, 0)
Sheets("sheet4").Range("j2") = WorksheetFunction.Sum(Range(Cells(2, s), Cells(lr, s)))

 

[Fluff]

Why do you need to make any changes?

 

[fadee2]

I want to make the lookup value more dynamic, with

minday = Application.WorksheetFunction.Min(ActiveSheet.Range("fulldates"))

where fulldates is a named range on another sheet.

Nevermind though, ended up creating the right piece of code.

minday = Application.WorksheetFunction.Min(ActiveSheet.Range("fulldates"))
Set look_mon = Sheets("sheet4").Range("b2:f2")     'lookup range
If IsError(Application.Match(minday, look_mon, 0)) Then
av = Format((grandtotal) / Application.WorksheetFunction.NetworkDays_Intl(minday, maxday, 11), "#,#.00")

Else

s = WorksheetFunction.Match(minday, look_mon, 0)
t = WorksheetFunction.Sum(Sheets("sheet4").Range(Sheets("sheet4").Cells(3, s + 1), Sheets("sheet4").Cells(lr, s + 1)))

av = Format((grandtotal + t) / Application.WorksheetFunction.NetworkDays_Intl(minday, maxday, 11), "#,#.00")
End If

Thanks for the help....

 

[Fluff]

Glad you sorted it & thanks for the feedback.