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:


    Code
    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!


    HestiA

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

    Code
    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!