Error 13 Type Mismatch in For Next Loop

Hello! I have written a code that checks a value in one column (Column "C") and does a vlookup for corresponding values to put in other columns (Columns "AH" to "AM").
Using a For Next loop it does this until it has checked all the rows from the 3rd row to the last row with data in it.

Within the loop I have several If statements: first to check the "C" column value that I'm using to do the lookup with meets certain criteria (i.e. the cell isn't empty, and doesn't say "Product ID").
All of my code worked fine until I added a second set of criteria based on the destination cell. I don't want the code to update cells that already have a value in them, unless the value is #N/A (i.e. Error 2042).
Here is the code:

Please Login or Register  to view this content.

It seems like VBA has no problem executing code like line 7:

Please Login or Register  to view this content.

But as soon as the cell to check is the target cell and not the lookup value, it gives a Type mismatch error:

Please Login or Register  to view this content.

The code also breaks at line 18:

Please Login or Register  to view this content.

Maybe that's not the problem, but that's the only thing I can see different between those very similar lines of code.

Any solutions

it may not be causing the problem though i dont think you can redim x as you are using x as the loop value.

also i would not use empty though again this may be ok ,, Vbnullstring.

and there doesnot seem a need for

Please Login or Register  to view this content.

Thanks for your post, which I will comment on below:

it may not be causing the problem though i dont think you can redim x as you are using x as the loop value.

It is possible to redim x, I tested it by making the value of n outside the range of the loop (3 to ttl). As soon as VBA hits a row that meets the criteria it will redim n to the new value (5000) and end the loop, since the new value falls outside of the range for the loop.

Thanks for pointing that out though, because my code was telling the loop to skip 1 row with "n = n + 1". So I will change that section.

also i would not use empty though again this may be ok ,, Vbnullstring.

Empty is fine, it works in line 7. vbNullString is for Strings, while Empty, or vbEmpty is for Variants. Since a string could be a variant, empty should be fine. What should not be used is "", i.e. a zero-length string.

and there doesnot seem a need for

Please Login or Register  to view this content.

Yes, these lines do serve a purpose. If the cell with the vlookup value is empty, there is no way for the vlookup function to be successful. I already know that the corresponding values won't be found, because there is no criteria to lookup with. VBA will then fill my target cell with "#N/A", but I'd rather have these cells blank. That way when I see error 2042, I'll know that it's because there is a lookup value but no corresponding value to be found.

I will repost the code here with the changes:

Please Login or Register  to view this content.

I still need help with the original line that breaks, line 11. Can anyone help me?

Thanks

QUICK NOTE: "n = n + 1" was referred to as "redim" but this is technically incorrect.
The Dim statement declares the variable, i.e. tells VBA to set aside memory for a certain variable type during the execution of a procedure.
The equal sign initializes the variable, i.e. gives it its value, for example n = 100.
By changing the definition of n to n + 1, it doesn't affect the Dim statement, whose purpose was just to set memory aside.
The term "redim" is exclusively to resize space assigned to an array.
Thanks

Hi romperstomper, just saw your posts now.
I will have to wait until tomorrow to reply, as I have to go now :-(
But thanks for the posts and I will try out your ideas tomorrow :-)

Wow romperstomper, this is some tight code!
Very good ideas, I like the .ScreenUpdating = False, makes the code run much faster!
Using the Application.Match property instead of vlookup doesn't result in a lot of cells filled with "#N/A", so I don't even need the code to look for error 2042 and replace it!

I think you have that the wrong way round.

What should not be used is "", i.e. a zero-length string.

Oops! Yes, that is what I meant to say...

Why would you not use "" or vbnullstring?

Zero-length strings take up 6 bytes, vbNullString takes up 0 bytes:
http://www.songhaysystem.com/kb/numb...41/subject/vba

Thanks for your help!