finding key word offset and repeat. plus move

Hey everyone,

I have been trying to automate my weekly reports while have created a workable solution, I am struggling with finding the best way complete my weekly tasks via VBA.

Since my Reports are non-standard and does not have a unique identifier that I can reference. have decided that if I do a keyword search then offset from the word by 1 to 3 columns to extract the data, then place the value into a master sheet. I have several issues. 1. Have to report this action 30 times "aka third different word searches" 2. what happens when I move to the next day since all values currently linked to the previous day location. 3. issue i have is if the word doesn't exist at all.. it dumps me out the script. 4. as you can see I have to keep calling the Sheet or I get an error.

If I stay on this paths, I will have hunderds of line of code, since I would have to repeat this section of Script 30 one for each item I am trying to extract than start over because the location of changes because value location would have to change so it doesn't over write last nights data.


Any thoughts or suggestions?

thanks

Please Login or Register  to view this content.

mehmetcik, Thank you for the quick response code it was very helpful, I believe I understand what the code is doing you are use the MyArray to cycle thru all key words for a particular day. The keyword offset is in the array along with final destination, While stepping thru the code I noticed that the Destination is not being populated.

Another area I would like your input on; do you think it would be best to put in a input box to enter in the name of the sheet or do you think it would be better to dump the files into a folder and have the Script pull the worksheet data then add it to the next empty line in the master spreadsheet?

Thanks again for your help and support as I learn and get a better understanding

-BB