VBA: MATCH with multiple criteria to return row number

[MadeFromMetal]

I'm trying to use VBA to use MATCH to return a row number. Then use that row number in the same Worksheet_Change macro.

If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim lc As Long, fr As Long, n As Long, nr As Long
Dim fc As Range, sc As Range, stl1 As Range, stl2 As Range
Set fc = Cells(Target.Row, 1)
Set sc = Cells(Target.Row, 2)
Set stl1 = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(-1))
Set stl2 = Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(-1))

With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIfs(Columns(1), fc, Columns(2), sc)
  If n = 1 Then
    lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    If lc > 1 Then
      Cells(Target.Row, lc + 1) = Format(Now, "m/d/yyyy h:mm")
    End If
  Else
    fr = 0
    On Error Resume Next
    [B]fr = Application.WorksheetFunction.Match(fc & sc, stl1 & stl2)[/B]
    Debug.Print fr
    On Error GoTo 0
    If fr > 0 Then
      lc = Cells(fr, Columns.Count).End(xlToLeft).Column
      If lc = 2 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc = 3 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
    End If
      Cells(Target.Row, 1).ClearContents
      Cells(Target.Row, 2).ClearContents
    End If
  End If
On Error Resume Next
  Me.Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Me.Cells(nr, 1).Select
  .EnableEvents = True
  .ScreenUpdating = True
End With

End Sub

When the bolded code reads

fr = Application.WorksheetFunction.Match(fc, stl1)

The Worksheet_Change macro works fine until there happens to be a duplicate value in column A. I need it to match the values in columns A & B respectively and make the changes in the appropriate row.

When the bolded code reads as is, the debug.print returns a 0.

I have also tried

fr = Application.WorksheetFunction.Evaluate("=MATCH(" & fc & "&" & sc & "," & stl1 & "&" & stl2 & ")")

Any help is appreciated!

 

[mart37]

You refer to value in stead of address.
fc ==> fc.Address