VBA - Macro - Loop - Data - find "normal text" IE. File name - replace list exhausted

[Jack7774]

I am new to the loop function/command in VBA. I was curious if VBA is capable of writing a macro to find within its own macro certain data like a "MMDDYY" and replace is with other data you give it from a list then run the macro and loop until the list of data you give it is exhausted.

For example, In the follow code I want to replace "MMDDYY" with the number one item on the list seen below then run the code but once the macro has ran this set of code loop again replacing the "MMDDYY" again with the second number on the list looping until the list is exhausted.
1) 010113
2) 010213
3) 010313
4) ...
5) and so on.

Please Login or Register  to view this content.

[JBeaucaire]

Assuming:

1) The list of codes is on a sheet called CODES starting in cell A1 and going down
2) The list is not created from formulas, that the list entries are real numbers in column A

This basic loop would run through all those numbers:

Please Login or Register  to view this content.

[Jack7774]

I don't doubt this would work but that would mean i would have to put a sheet names codes into each one of these workbooks i am creating. This does not work for what i had in mind.

Could I have the list inside the macro instead of on a sheet named codes? Each file I am going to be opening is not going to have a sheet named codes.

[Jack7774]

Could I use the same code but modify it slightly to have the list inside the after the = in this code you made?

Please Login or Register  to view this content.

[JBeaucaire]

No, you would not be putting the list in all your workbooks, just one. It would go in the main workbook from which you launch this whole macro. The first thing your macro might do is create a new blank workbook, then import all these text files into that workbook. That workbook wouldn't have any VBA in it when it was done and saved. Your list and this macro are in a separate, permanent file.

[Jack7774]

So since the macro is located in the personal file then I would need to create a spreadsheet called CODES and in cell a1 would be my first dtRNG or date in the list and then I would just continue down in column a until my list of dates is complete. Do I understand this correctly?

[Jack7774]

Okay so I wanted to test it out and here is the code but when I opened excel it didn't run the macro.

Please Login or Register  to view this content.

I have '020113 in cell A1. It works if I run the macro from macros but it doesn't open and run automatically though.

[JBeaucaire]

What kind of workbook is it in? Where is this macro in the modules?

[Jack7774]

I open excel and personal workbook starts thats where a sheet named CODES is and that is also where my macro is. I am not sure what you are asking when you say where is the macro in the modules. I think where else could it be but in the modules.

[JBeaucaire]

I small amount of Googling on this topic leads me to believe the Auto_Open macro in the Personal.xls is problematic in some uses, perhaps this is one of those. Try changing it to a Private Sub Workbook_Open() event macro and placing it in the ThisWorkbook module.

Also, the LISTS page was added to the Personal.xls workbook, correct? You might update the macro to cite that:

Please Login or Register  to view this content.

[Jack7774]

Correct. I put the auto open part aside for now because today is the day I need this to work. I tried to make this work with multiple dates as certain files have dates that have / and some without. I also have a folder that changes as well. Anyways here is the total code that i developed based on my understanding of what you wrote and what i thought would work this morning however it errors and says "Compile error: Invalid Next Control variable reference". This is only part of the code because it is to many characters for the post.

I open excel and it opens personal. On Sheet1 I put in cell A1 02/01/13, B1 020113, C1 2013-02. I presume my understanding of Dim, DtRNG, Dt, and Next are coded incorrect here. I think you get the whole picture now of what i am trying to do. I have trouble explaining things sometimes and I apologize if I was confusing before or now. Thanks again for all your help.

Please Login or Register  to view this content.

[JBeaucaire]

What's the DtRNG2 and 3 about?

[Jack7774]

Again I am unsure how DtRNG or Dt works. I understand it will work with a list seen on Sheet1 in column A. What if I have multiple dates within the macro such as DtRNG1 is the first list of dates in a certain format and DtRNG2 is the same date but a different format and DtRNG3 is to change the folder date as mentioned in cell references made above A1 =02/01/13, B1 =020113, C1 =2013-02. Does that make sense?

[JBeaucaire]

No, sorry. You're allowing your process to be more chaotic than I can follow easily.

[Jack7774]

I apologize for making it seem chaotic. I don't think it is to complicated. Forget vba logic for a minute and think about it like this.
I need X or (Dt1) to be replaced with the first constant in list 1 or (column A)
I need Y or (Dt2) to be replaced with the first constant in list 2 or (column B)
I need Z or (Dt3) to be replaced with the first constant in list 3 or (column C)

Then run the macro.

Next X, Y, Z in lists run macro again until list 1, 2, 3 have no more constants.

Essentially I have the entire month of february to rollfoward each day.
In column A would have 020113 through 022813 it needs to be in the MMDDYY format
In column B would have 02/01/13 through 02/28/13 it needs to be in the MM/DD/YY format
In column C would have 2013-02 this would stay the same all the way down the list until the last constant in columns A and B run out.

Does that make sense?

[Jack7774]

solution found here

http://www.excelforum.com/excel-prog...ng-thread.html