Replacement Command help

I work for a company that is required to keep documentation of all files it has submitted online in an excel 2007 worksheet. Each category for the file (assume placement or type of file) is in column A and each time we submit anything online it is only in one category, so I need to exclude the ones I'm not using. There already is a filter we can use for that but I add this in case it affects the script. Once uploaded, the one that uploads it is require to go into the file and type his initials into each file that was sent. The file name is located in column E and I have been using the "find" option to locate them. Each initial is in column O with the date entered (always done in a same-day block) being column P. I was wondering if it was possible to create a script that would place my initials and the date in those store numbers in a quicker and more efficient way. The numbers as we are given are unfortunately out of order so it takes longer because of that as well. Any help with this would be greatly appreciated, and sorry for the unnecessary paragraph.

Sorry, I don't quite understand your question fully. I get that you are trying to more efficiently add your initials and the date next to files but I'm confused as to what the criteria of the files is. If you could explain the criteria a script for this is quite simple.

Thanks!

RVasquez

Sorry in advance for having to write another paragraph to explain. I have to upload a large # of files (400-2500) once those files are finished to a website. Before uploading I create a screenshot printout of all the files names so I can check them off as I go. Once that is done, I have to go through each file name in the printout (Ex: 3 Bay - 99,87,55,90 - Str xxx -AA 01.2012.file type) and find them in the excel file based on the number following STR since it is the only unique piece in the series. I have a folder with all of the files still in it, so it is theoretically possible to sort them by the number following the str but I'm not capable of doing that. As far as updating excel, I have to first sort by category to narrow down the ones that I won't need (there are 5) and then find each individual one to initial from the remaining ones. The number following str is the number that is found in column E and each row down would obviously be a different file.

Okay so if I'm getting you right, you are importing data into an existing file and you want to put your initials and the date it was added next to those files as they are added, is that correct? If so, could you please provide a sample of you workbook with all personal information removed so I can write the code for you? Thanks!

RVasquez

I am not actually importing any data. It is saved on a hard drive that can be accessed by anyone in my department and I am simply going in and adding two letter to column O and a date to column P. I suppose i just need a script that can add those two things to columns O and P when i give it the appropriate number in column E. As I don't have a way to give it all the numbers at once because of annoying file naming procedures, I would have to do this by individually putting in the numbers to the code anyway. Also, I tried to upload but the amount of data on it is apparently significantly over the data limit for this website even with all numbers and info cleared

Is it possible to make a mock file, copy a small amount of data into a new workbook and save the new workbook and upload?

This is a version that is lacking the macros that the company had built in to allow for data sorting unfortunately. That was what had taken up so much room in the file. This is just an unfortunately weak mock-up.

Okay so first you sort by category correct? Then you find the files how? What column are you searching for your information in and is there consistency in the file name?

Yes, the first filter is by category. Once every unnecessary category is filtered out, only the one you were searching/using is left. The others are hidden. The files are then listed by Store Number going from smallest at the top, to largest at the bottom automatically. It is possible to sort by each of those columns by whatever has been filled in them. For instance, if i had placed my initials in uploaded before, I could choose to show only those that already had been initialed by me, or I could sort by what is remaining blank. The files are labelled like my example above, 5 bay - str 4567 - jw.2012etc... The number following str is the one that the files are differentiated by. There is only one file per store, but there can be multiple stores under 5 bay for example.

so any row that is blank in the initial column you would like to add initials to after the first filter correct?

no, unfortunately. Which is why i was unsure if this was possible. That would be easy enough for me to handle if it was only that. After the initial filter, There are still only specific ones that I want to add that too, but i believe they would have to be input by hand into an if-then code. I was hoping to figure out a way to type a number into the code and have it fill in the initials and current date in the correct row correlating to that number in the store number column. So theoretically, I would type in a store number like 555 into the code and that would then search for a store number with that name and fill in the initials and the date in that row under the correct columns. If it isn't possible without outside programming (i.e a seperate and outside code that i might not have access to) that is fine.

That actually is exactly what i was looking for. Is there a way to make it pop up over and over again? It popped up when i loaded the excel file for the first time, but never came back after that. I tried running the macro again but i assume it continued to try to fill in that same slot again and again

It should be set up so that once you select another worksheet and then come back to the first worksheet it should reopen.

You could always add an ActiveX command button and when you right click and view code insert the line

Please Login or Register  to view this content.

Then make sure you're off Design Mode and when you click the command button the userform should appear.

It unfortunately won't pop up again no matter what i do to that file (reopening or whatever else). I also tried the button creation but I can't get it to actually click one that is created. I made the button but the only options that come up are to click and drag it, or edit the code

Are you using the file i sent you or have you imported the code and form into your workbook?

I can't use that exact same file because it is a pre-made one that I would just be importing code to. I tried using both though. I couldn't get the command window to pop back up even on your original.

If you open up the excel file that I uploaded directly from the forum again does it work? Also, could you check to make sure the Design Mode is not clicked on the Developer tab?

Yes, it will open once each time I open the file, but never again after that. I likely would need to be able to do it over and over again a lot of times because of the sheer number of different store numbers. Also, we always filter by category name as opposed to category. Sorry i didn't include that originally. I unclicked design mode, but the button where i had copied the macro in gave me an error

could you save the copy you are working with on your computer? And then I'll take a look at what is going wrong? It should repopulate everytime you navigate to another worksheet and then reselect the original worksheet. It's working on my end so I'm not quite sure why it is not working on your end. The combobox values are populated from Column C for the category and E for the Store Number.