vba code for matching a cell with range of cells in a different worksheet.

[fthomas]

HI. I am new to vba and i am trying to compare a value in sheet 1 which is a date eg. Aug 2022 (coming from a formula) with sheet 2 where i want to insert the Aug 2022 value if its not already there.
My code looks like this .. The Application match is not able to match values and returning False.Can someone please help

Sub Button16_Click()
'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination)
'Sub sbCopyRangeToAnotherSheet()
Dim CellMonth As Variant
CellMonth = Application.Match(Sheets("Month Over Month").Range("a1:$a$2000").Value, Sheets("Income and Expense").Range("c20").Value, 0)
MsgBox IsError(CellMonth)

If Not IsError(CellMonth) Then

Sheets("Income and Expense").Range("$L$17").Copy
Sheets("Month Over Month").Range("B").End(xlUp).Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Else
Sheets("Income and Expense").Range("$L$17").Copy
Sheets("Month Over Month").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False
End If
'End Sub
End Sub

 

[ZMyrrh]

You should try changing "Application.Match" to "Worksheetfunction.Match" and see if that works. After seeing a potential error I'm going to write this out. Worksheetfunction.Match(Sheets("Income and Expense").Range("c20").value, Sheets("Month Over Month").Range("a1:$a$2000"),0)

Tell me how this works.

Best of luck!

 

[fthomas]

Thank you for the resonse, I was able fix this by below

Application.Match(Sheets("Income and Expense").Range("a12").Value, Sheets("Month Over Month").Range("a:a").Value, 0)

You should try changing "Application.Match" to "Worksheetfunction.Match" and see if that works. After seeing a potential error I'm going to write this out. Worksheetfunction.Match(Sheets("Income and Expense").Range("c20").value, Sheets("Month Over Month").Range("a1:$a$2000"),0)

Tell me how this works.

Best of luck!