Receiving runtime error 1004: Method 'Range' of object '_Worksheet' failed

Hello all,

I am trying to write a macro to do the following, I have two very large lists (10-20k) rows each which I need to sort through a match up to each other based on a reference number. My though process so far has been to make a four sheet workbook with two of the sheets containing one of these lists I have each. I the pull the first number from the first list and then compare it to every value in the second list. When it finds a match it copys the row from each sheet and pastes them in a third sheet side by side. When I initially tackled this problem I used multiple steps to accomplish this copy paste action. The code which I wrote for this I have left in the code below however it has been commented out. This multi step process did work however because of the volumn of data I have I am trying to make this macro run as fast as possible so I am now trying to do the copy paste in one command.

The line which I am receving the runtime error message from is:

Please Login or Register  to view this content.

When I mouse over this line during debugging it reads:

matchWS.Range(tCount, 4).Value = <Method 'Range' of object '_Worksheet' failed>

Which leads me to believe that the error is happening in the latter part of that line. What I cannot seem to figure out is why, when the exact same thing seems to me to be working fine in the first part.

I thought maybe something in the second part was returning a value which I did not expect but when I mouse over the different aspects of the second half of that statement nothing seems to be return anything odd.

deductionWS.Cells(xCount, 1) = 6132010
xCount = 2

deductionWS.Cells(xCount, 1) = 9900004
xCount = 2

Those values all match the second line of the the worksheet I am calling.

I've tried to be as specific as possible with calling these methods and making sure that I reference the worksheet which I want the method to be called for but I cannot seem to make any head way into why I keep getting this error. I'm just plain old stumped haha.

Below is the full code for my macro so far however I will admit right now it is not complete, and yes I know I need better variable names

Once I am able to figure out a better way to do the copy paste or able to fix my current method I will have to add the code to copy the data from the second list as well.

Please Login or Register  to view this content.

You are using Cells syntax with the Range property. Change it to:

Please Login or Register  to view this content.