VBA coding concatenate, copy, paste, remove...

Hello again all,

Today was my first attempt to completely write a new code for my job to see if I could do it on my own, and to say the least, I failed.

Here is the task I am trying to perform:

- remove ".pdf" from end of file name in columns "E", "I", and "AE"
- clear contents of column "AD"
- any cell in column "AP" that is not blank, add ".dwg" to the end of the text

And now here is my code that is failing

Please Login or Register  to view this content.

I am getting an error that says "For without Next" and I have no idea why. As you can probably tell I am completely new to all of this so I could really use a few pointers as to how to get this code (or your own) to work.

If you have any tips or anything for me as well to explain how I could have attacked this better that would also be greatly appreciated.


Thanks again and have a great weekend!


Mike

Hi Mike, you are getting the error because of

Please Login or Register  to view this content.

there is no

Please Login or Register  to view this content.

Well, I'm guessing by the code that you've supplied that it should look like

Please Login or Register  to view this content.

Also, there are multiple ways to get something done using VBA. In saying that, if your code works well, and by well I mean with no errors and runs fast, then I would say that the code is fine.

Hi sk8shorty01, I could possibly do as you ask but it would be beneficial to us both if you upload a mock file. The way the code is now, I can debug it without error but I don't know if it will run properly at run-time.

Hi sk8tshorty01, put the following code into a standard module and let me know if it fulfills your requirements.

Please Login or Register  to view this content.

I have revised this code at 11:39 Central Canadian time.

Sorry about the headers, if you'll notice I revise the code a little in post 10 but here is the code with AE instead:

Please Login or Register  to view this content.

The reason you were getting an error is more than likely because of the following line

Please Login or Register  to view this content.

, specifically,

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

. Copy and paste the full procedure from this post and let me know!

Originally Posted by sk8shorty01

is that telling it how many rows to count? What does the 30 or 31 designate? Is that how many cells to move in each row to check for values?

So AD would be the 30th cell in the row and AE would be the 31st?

That is my best guess anyways.

It is actually telling the code which Column to set. For instance, Range A2 would be

Please Login or Register  to view this content.

where the 2 is the rows count and 1 is the columns count. That being said,

Please Login or Register  to view this content.

means that the code will search for the last row

Please Login or Register  to view this content.

in column AE (or 31).

Does this make sense?

So you want to remove AD as well? For sure? if so, just add

Please Login or Register  to view this content.

I didn't test that but it should work, if you want to remove AD as well as AE. Don't forget, once the data is cleared using a procedure, there is no going back to retrieve it.

Here is your full Procedure, revised to remove copy/paste

Please Login or Register  to view this content.

You are welcome.