Runtime 424 error

I am encountering a runtime 424 Object required error error with the following code and tearing my hair out. Any help in identifying the possible reason for the error would be much appreciated.

Purpose: The code takes data from a particular cell, checks whether its 'AAA' or 'BBB' or not and if it is taking the next cell and concatenating __1111 to it. The concatenated value is then posted in a separate cell

Dataset (last two columns are used)
NV B AEX AAA 123
NV B AEX AAA 456
XO B CAXE BBB 312
XO B CAXE CCC 334
BL B EDXE DDD 555

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

When asking help about an error message, always indicate the line of code where it is occurring. I do not see an obvious cause of the error you quoted, and do not have your file to be able to run a test. Can you indicate the line, or attach your file?

Also, I will bet that your declarations do not do what you think they are doing. For example,

Please Login or Register  to view this content.

does not declare four variables as Integer. It declares filledrowcount as Integer, and defaults all the others to Variant. Please see this excellent page on declarations.

This line of code works perfectly well but is deceiving in intent:

Please Login or Register  to view this content.

A much cleaner and obvious test would be

Please Login or Register  to view this content.

Using ActiveSheet unnecessarily can introduce bugs that are hard to diagnose. In this case you are always using Worksheets("Sheet1"), so there is no need to active it and refer to ActiveSheet. I recommend you modify your code as follows:

Please Login or Register  to view this content.

It should be a comma between rowcount and colcount1 in Cells.

Please Login or Register  to view this content.