Macro DtRNG as Range, Dt as Range, Multiple Criteria or dates

I used to modifiy my macro each day and change the date then run the macro. Basically CTRL-F find/replace AABBCC with the corresponding MMDDYY date for that day then run the macro. I found out that this code...

Please Login or Register  to view this content.

...would pull one date from the list in column A then run the macro and rinse and repeat for each Dt in DtRNG. The problem I am running into is I don't just need one date replaced. I need six different dates replaced. Is there a way I can replace the multiple dates without having to use CTRL-F to modify the macro each time. Below is pieces of the code that reference each date. Date1 and Date11 are the same date but different formats. I know I can use the above code to replace one of those dates but what about multiple dates?

Please Login or Register  to view this content.

Here is my thought process but its stops at one date

Please Login or Register  to view this content.

I don't need the loop for this macro I just took it from another macro someone made for me. I am not sure how else to explain what I am trying to do other than tyring to replace DATE1 and DATE11 with one date (DATE1 and DATE11 being the two formats I need it in) {Im not concerned with the formats I know how to do that its dimming them down into a way I can use it from the personal.xls columns A and B and so on i guess, DATE2 and DATE22 with the second date, and so on until DATE6 and DATE66. I know .....

Please Login or Register  to view this content.

...will work for one date but what about the other 5? Can I do something like DtRNG and DT to be column A, then DtRNG2 and DT2 to be column B or something like that so that I can type the dates I need in personal.xls file in columns A and B and so on instead of using CTRL-F find/replace ?

I have no clue how to explain it. I use CTRL-F to find and replace the word "DATE1" with example 020113 that is a date. I do the same thing for DATE11 and its the same date 02/01/13 but in this format.

'CTRL-F REPLACE "DATE1" W/1ST DATE OF WEEK, FORMAT MMDDYY
'CTRL-F REPLACE "DATE11" W/1ST DATE OF WEE, FORMAT MM/DD/YY
'ETC...........................................................
'CTRL-F REPLACE "DATE6" W/1ST DATE OF WEEK, FORMAT MMDDYY
'CTRL-F REPLACE "DATE66" W/1ST DATE OF WEE, FORMAT MM/DD/YY

I have another macro that basically does this very thing except it only does it for one date and I have multiple dates in this macro. Here is a small piece of it

Please Login or Register  to view this content.

All I have to do is type the date in cell A1 in the personal.xls sheet1 tab. I want to do something simular except i have 6 dates total that need to be replaced in the weeklyintegrationsetup macro.

That directory will change based on the date as well.

The directory changes. Each month a folder is made for that month. I don't know how to declare a constant or even if that would work with the directory changing each month. Closing them once I'm finished with them do something I am not aware of rather than doing it at the end? Speed up the processs slightly or something?

Thats all the macro does is open files copy data from them and paste them into template. I am not sure how you are missing that part.

I see you shortened the code, nice job. I didn't know the code language well enough to write it that way but i see now how each item works and what it does. The only problem I see so far is that

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Still have the DATE1 and DATE6 in the file name which is the part I want to replace with each week's weekdays dates. Otherwise its shorter and looks awesome so far.

Yes i manipulate the file name because the file name has a date in it that changes for each days date for each file name. And as far as using constant or variables i don't know how but that sounds like a plausible solution. As for what the code does it formats certain data, it enters a date, and it copies. I'm not interested in shortening it unless you feel you want to but the part that i am trying to achieve is avoid having to open the macro hold button control while pressing f using the find replace the date 1 date 2 date 3 date 4 date 5 date 6 into the actual dates which is the actual file name. If i could type the dates in as a constant or variable at the top that would be great but how do you do that?

So I would say what

Const "?" = Date1
Const "?" = Date2
Const "?" = Date3
Const "?" = Date4
Const "?" = Date5
Const "?" = Date6

What about format if i do it this way?

Also could i set the Const "?" = "A cell reference in the personal.xls sheet1 like B1 and put the date in the cell then manipulate the format from that point?

I can also tell you what each line is doing as well if its not written how you are used to reading it. LOOK FOR THE COMMENTS IN ALL CAPS AND WITH THE ' STARTING THE LINE AS WELL. MAYBE THIS WILL HELP.

Please Login or Register  to view this content.

Attached is a sample file. Basically that part of the code copies the data in that cell and essentially find the bottom of the column of a row that still has data and pastes the data in that cell in column a in that row then copies the data in the rest of the column. I wrote it this way because each day these files change in size and I don't know which cell it will need to copy and paste down to so that each row of data has that following date. I suggested to a superior that we just contact the company who made these reports just for us and add a date column on the report however its not very high on their priority list.

I can't give you the data unfortunately. If you would like to fill in the columns with data yourself you are more than welcome. I fail to see how you won't be able to see what the code is doing when the code itself tells you line by line what it does. I would suggest opening the sample file and running this code line by line intead of running the macro all at once to get a better understanding. I have put comments in the code line by line almost to help.

Please Login or Register  to view this content.

Does this help?

Originally Posted by jessebranum777

So I would say what

Const "?" = Date1
Const "?" = Date2
Const "?" = Date3
Const "?" = Date4
Const "?" = Date5
Const "?" = Date6

What about format if i do it this way?

Also could i set the Const "?" = "A cell reference in the personal.xls sheet1 like B1 and put the date in the cell then manipulate the format from that point?

I would still like an example of what you mean in order to setup those dates as constants as to what goes in the ? mark area I have no idea.

Thanks. I read some more forum posts about how to use constants as well. SOLVED