Macro compatibility issue from excel 2016 version to 2010 version

[Daddy143]

Hi all,

i have a code written in excel 2016 version and same was pasted in excel 2010 version. when i run the code i got this error "Excel VBA Run-time error '438': Object doesn't support this property or method".

the code works perfectly in 2016 but not in 2010 because of "XMATCH" function not supports excel 2010 version.

my macro stops here "firs_inst = WorksheetFunction.XMatch(CLng(Mydate), Myrange, 0, 1)".

any help...

Sub copy()
Dim wb1, wb2 As Workbook
Dim WS_Count1, WS_Count2 As Long
Dim i, j As Integer
Dim lrow1, lrow2, lrow3 As Long
Dim Myrange As range
Dim Mydate As Date
Dim firs_inst, Last_inst As Long
Set wb1 = Application.Workbooks("tra.xlsm")
Set wb2 = Application.Workbooks("ARN.xlsm")


WS_Count1 = wb1.Worksheets.Count

For i = 4 To WS_Count1

    wb1.Activate

    lrow1 = Worksheets(i).Cells(Rows.Count, 11).End(xlUp).Row

Mydate = wb1.Sheets(i).Cells(lrow1, 11).Value
Set Myrange = wb1.Sheets(i).range("K:K")


    Sheets(i).Cells(lrow1, 11).copy

    WS_Count2 = wb2.Worksheets.Count

    wb2.Activate
    j = i - 1
    lrow2 = Worksheets(j).Cells(Rows.Count, 1).End(xlUp).Row
    If wb2.Sheets(j).Cells(lrow2, 1).Value <> Mydate Then
    Worksheets(j).Activate
    Cells(lrow2 + 1, 1).Select
    ActiveSheet.Paste
   
    lrow3 = wb1.Worksheets(i).Cells(Rows.Count, 2).End(xlUp).Row
    wb1.Worksheets(i).Cells(lrow3, 2).copy
    wb2.Worksheets(j).Activate
   
        With wb2.Worksheets(j)
            .Cells(lrow2 + 1, 8).Select
            .Paste
            .Cells(lrow2 + 1, 14).Select
            .Paste
        End With
       
firs_inst = WorksheetFunction.XMatch(CLng(Mydate), Myrange, 0, 1)

Last_inst = WorksheetFunction.XMatch(CLng(Mydate), Myrange, 0, -1)

wb1.Worksheets(i).Cells(firs_inst, "k").Offset(0, -6).copy
   
wb2.Worksheets(j).Activate

Cells(lrow2 + 1, 15).Select
ActiveSheet.Paste

wb1.Worksheets(i).Cells(Last_inst, "k").Offset(0, -5).copy
   
wb2.Worksheets(j).Activate

Cells(lrow2 + 1, 16).Select
ActiveSheet.Paste

        'lrow4 = wb1.Worksheets(i).Cells(Rows.Count, 2).End(xlUp).Row
   
    'wb1.Worksheets(i).Cells(lrow3, 5).copy
    'wb2.Worksheets(j).Activate
   
  'With wb2.Worksheets(j)
            '.Cells(lrow2 + 1, 15).Select
            '.Paste
        'End With
    'wb1.Worksheets(i).Cells(lrow3, 6).copy
    'wb2.Worksheets(j).Activate
   
        'With wb2.Worksheets(j)
         '   .Cells(lrow2 + 1, 16).Select
          '  .Paste
        'End With

    'ActiveSheet.Paste
Application.CutCopyMode = False

Else
End If

Next
Exit Sub
End Sub

thanks,

 

[Fluff]

That code should only work if you have a 365 subscription. It will not work in any stand-alone versions of Office.
Just remove the X & use Match

 

[Daddy143]

That code should only work if you have a 365 subscription. It will not work in any stand-alone versions of Office.
Just remove the X & use Match

Thank you Fluff

I removed the X and runs the code and got the below error.

Compile error:
Wrong number of arguments or invalid property assignment

 

[Fluff]

Forgot to mention that you need to remove the last argument, although the match function cannot look from bottom up, so you will need to use lookup or Range.find for the Last_inst