Inserting a Column - Sometimes Works, Sometimes Not

I have had every part of this project working at some point, but for some reason Excel seems to decide every so often that it wants a different syntax for inserting a column. Initially, I used this:

Please Login or Register  to view this content.

That worked great for a little while, then after working on some other parts of code, it changed its mind and for some reason decided not to like that syntax. It would select the correct cell, but wouldn't do the column insertion. In desperation, I tried a little re-ordering.

Please Login or Register  to view this content.

This inexplicably worked, but I wasn't asking any questions. I kept working, and now it doesn't like either form, nor will it take

Please Login or Register  to view this content.

For context, let me explain the whole project. There's a sheet "instruments" that contains four columns: the vendor part number, the internal part number, the vendor name, and the row in the sheet "Sheet1" where the internal part number appears. There's a sheet "pivot" that comtains a column for each vendor and records the maximum number of vendor part numbers that correspond to one internal part number. Finally, there's a sheet "Sheet1" that has all the vendors as column headers and the internal part numbers in the leftmost column.

What I have to do is fill in Sheet1 - for every vendor-internal part number combination, put the vendor part number in the appropriate cell. In some cases there are duplicates, and in this case, we want to create an additional column for it. For instance,

----------- | VendorName
Internal P/N | ABC123, DEF456

Becomes

----------- | VendorName | VendorName2
Internal P/N | ABC123---- | DEF456

Here is all the code in its current form together:

Please Login or Register  to view this content.

I tried to comment it well enough that it would be obvious what each part is trying to do. If anyone has any ideas as to what the deuce is going on here, I would really appreciate some help. I just learned to program Excel macros yesterday, and this problem is really confusing the heck out of me.

Alright, apparently it's some odd formatting error that was caused by my clearing all of the cells in the first portion by setting their value to empty string. By using the Delete method instead, it works as desired.

Next you need to learn to debug. Did you know you can set breakpoints, monitor variables in the Watch window, single step through code, ...?

Much easier than all those MsgBox statements.