I want to paste data in an array which I saved from another workbook to a specific cell. The destination cell is chosen on 2 matches:

  1. Row position is got from the code column
  2. Column position is got from the day row

With this cell information, I want to paste data to that field. It is in a loop, because I have to enter 9 of those data to respective cell.

Sub copyData()

    Dim source As Workbook

    Dim Machine(1 To 9, 1 To 2)
    Dim r As Byte, c As Byte
    Dim day As Byte
    Dim rowCode As Long
    Dim colmnDay As Long

    Const StartRow As Long = 6
    Const StartColumn As Long = 1

    Set source = Workbooks.Open("C:\Users\Sabareesh\Desktop\MONTH_DATA.xlsx", True, True)

     day = source.Worksheets("day 1").Range("W20").Value

    For r = 1 To 9
        For c = 1 To 2
            Machine(r, c) = source.Worksheets("day 1").Cells(r + StartRow, c + StartColumn).Value
        Next c
    Next r

    ThisWorkbook.Activate

    For r = 1 To 9
        For c = 1 To 2
            colmnDay = WorksheetFunction.Match(day, Worksheets("sheet1").Range("F1:J5"), 0)
            rowCode = WorksheetFunction.Match(Machine(r, 1), Worksheets("sheet1").Range("E2:E9"), 0)

            Worksheets("sheet1").Cells(rowCode, colmnDay).Value = Machine(r, 2)


            'Let row1 = "A" & r
            'Let row2 = "B" & r
            'Worksheets("sheet1").Range(row1).Value = Machine(r, 1)
            'Worksheets("sheet1").Range(row2).Value = Machine(r, 2)
        Next c
    Next r


    Workbooks("MONTH_DATA.xlsx").Close SaveChanges:=False
End Sub

It does: Store these variables "day, Machine(code, value)" successfully from the workbook MONTH_DATA by opening it. VBA is running from another workbook.

Expected result: match() needs to get me the row & column position of matched code & day. With that cell info I can paste the value into it.

I get an error

"Unable to get Match property of WorksheetFunction class"

at line

rowCode = WorksheetFunction.Match(Machine(r, 1), Worksheets("sheet1").Range("E2:E9"), 0)

0 Answers