VBA DtRNG Dt For each Dt then - bumping thread

[Jack7774]

I asked for help and after a few discussions I feel due to my explaining it incorrectly I made it to complicated. I didn't understand why no one else responded back since this other gentlemen couldn't help so I made this new thread because I thought my old one was void in a sense. I apologize for explaining it in a complicated way but again I am self taught VBA and I don't know the terminology very well.

Here is the old thread just in case it is needed

http://www.excelforum.com/excel-prog...html?p=3139470

The code I have is seen below. The DtRNG1, 2, 3 and Dt1, 2, 3 may not be correct in how i used them. Here is the idea behind what i am attempting to do. I have to roll the entire month of FEB but for each individual day not the month as a whole and what that means is each day has a file named

I:\ACCOUNTING\Jesse - Recon\ALL STATE Rollfoward\14400\14400 ALL STATE DAILY ROLLFOWARD " & Dt1.Text & ".xls

This file name changes with each new day corresponding to the days date in the format of MMDDYY thats Dt1.

Dt2 is the same day's date but in the format of MM/DD/YY
Dt3 is a folder date that changes with the month in the format YYYY-MM

The idea is for me to be able to put on Sheet1 of the workbook in column A a list of all the dates for the month of February in Dt1 format and vice versa in their corresponding format in columns B and C.

Once the data is on Sheet1 that I need, the desire is to have a macro that would insert the data from each list in columns A, B, C into the corresponding Dt1, Dt2, and Dt3 into the macro then run it and rinse and repeat until the data in columns A, B, and C were exhausted or the constants ran out. The issue when the macro is ran it errors and says "Compile error: Invalid Next Control variable reference" pointing to the Next Dt1, 2, 3.

Does anyone know how i can make this work or another way all together to fit the desired solution? It sounds like the other gentlemen had the right idea but gave up on me. Although I am not sure if he did or not. Can anyone else help me figure out how to make this work if possible?

Please Login or Register  to view this content.

[JBeaucaire]

You probably don't need thd Dt2 and Dt3 since they're merely variations on the same dates in Dt1. Just use the VBA FORMAT() method to draw each Dt1 data into the correct format.

All that is required is for each of your Dt1 strings to be ACTUAL correct dates.

MMDDYY

Please Login or Register  to view this content.

MM/DD/YY

Please Login or Register  to view this content.

YYYY-MM

Please Login or Register  to view this content.

So, go back to your original macro with only DtRNG and then use the method above the "adapt" the string for each use needed.

[Jack7774]

So if i understand you correctly all I need to do is add the code below before the code with Dt is ran each time it changes like this

Please Login or Register  to view this content.

However I think this next code would be a problem with this purposed solution though because it has two formats in one line because of the directory of the file name is one date format and in the file name itself is another.

I can't put two formats here at least i don't think i can.

Please Login or Register  to view this content.

I left the Dt1, 2, 3 in there for the time being so that you could see I need two different formats in the one line and so you could see the first file only has one.

[JBeaucaire]

No, you insert the desired Format() reference into the strings where previously you were trying to reference the Dt2.Text which never existed.

[Jack7774]

Is this correct?

Please Login or Register  to view this content.

[JBeaucaire]

No, using the original examples given, you string "pieces of text" together using the & sign.

Original:

Please Login or Register  to view this content.

New:

Please Login or Register  to view this content.

I can't think it's a good idea to put / in the name of an xls file. Format(Dt1, "MMDDYY") would be better for use in filenames.

[Jack7774]

Solution!!!