Problem with Error 13 Type Mismatch & variable with error 2042 without N/A values

  • Hi all,

    I am recently trying to build a macro to copy data from diff sheets and paste them into a mastersheet. However, I have some problems with a part my of codes which are used to find the values in the diff sheets.

    Here are my codes as follows:

    This is the row with the error:

    lastRow = intelSht.Cells(Rows.Count, ctyCol).End(xlUp).Row

    And ctyCol is the one having the error 2042, even though all my data rows do not have any N/A values. However, these rows contains formulaes, which I suspect might be the case of the issue.

    Is there anyone that can help with this issue?

    Thank you and have a great day!


  • Presumably the value is not found in the range in this line so that ctyCol is not a number.

    ctyCol = Application.Match(ctyName, intelSht.Range("2:1"), 0)

    Edit: in fact Match will only work on a 1d range anyway so Range("2:1") won't work (not sure what you are intending though).

  • Hi StephenR,

    Actually what i wanted to do is to copy data from these rows and paste them in a master sheet, which is in another workbook.

    Therefore i am trying to build a macro using range to find them and store them in the arrays i created, then calling them out and pasting them in a master sheet.

    Attached excel is an example of data that i want to copy.

    Hope this helps. Thanks :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!