Excel Vba select case compare Dates and put earliest to start and recent to end

[Surendran Setty]

I do not see any issue or error in Sheet1 when the function is used as formula in Cell B2 or C2.

However in Sheet2, I forgot to comment one line in code which I was using to test for one file only. However I still see the correct output, when I run 'Get Start and End for all Cycle Dates'.

I have run all the files you have sent. I do not see any issue now. Try the new attached file. The output is shown for all the files.

For Product type, I may not be able to do now as I will be on vacation from tomorrow for a week. With available code and logic, you can try yourself.

Regards,
Surendran

 

[mshome]

I do not see any issue or error in Sheet1 when the function is used as formula in Cell B2 or C2.

However in Sheet2, I forgot to comment one line in code which I was using to test for one file only. However I still see the correct output, when I run 'Get Start and End for all Cycle Dates'.

I have run all the files you have sent. I do not see any issue now. Try the new attached file. The output is shown for all the files.

For Product type, I may not be able to do now as I will be on vacation from tomorrow for a week. With available code and logic, you can try yourself.

Regards,
Surendran

Works awesomely enjoy you vacation
Thank you.

 

[mshome]

I do not see any issue or error in Sheet1 when the function is used as formula in Cell B2 or C2.

However in Sheet2, I forgot to comment one line in code which I was using to test for one file only. However I still see the correct output, when I run 'Get Start and End for all Cycle Dates'.

I have run all the files you have sent. I do not see any issue now. Try the new attached file. The output is shown for all the files.

For Product type, I may not be able to do now as I will be on vacation from tomorrow for a week. With available code and logic, you can try yourself.

Regards,
Surendran

hmmmmm
i see why i does't work at the office because we have 15+ text files
if i leave only few files it works, but when there is 15+ files i dose keep saying "01-Sep-15 is not found in both folder "
even i tried at home when there is more text files it says "01-Sep-15 is not found in both folder "

just copy paste the same files i gave you couple times to test this issue
it will not work
why is that ?

 

[Surendran Setty]

“just copy paste the same files i gave you couple times to test this issue”
Checked with more than 15 files placed in one folder. I do not see any issue.

"01-Sep-15 is not found in both folder "
Where do you see this error? In sheet1 formula or Sheet2? Could you zip and attach the files having the issue.

 

[mshome]

“just copy paste the same files i gave you couple times to test this issue”
Checked with more than 15 files placed in one folder. I do not see any issue.

"01-Sep-15 is not found in both folder "
Where do you see this error? In sheet1 formula or Sheet2? Could you zip and attach the files having the issue.

i made gif to show how i get the error and my logs files attached

 

[Surendran Setty]

In Log 2 folder files, only Cycle Date 01-Sep-15 exist.

In Log 1 folder files, Cycle Dates 01-Sep-15, 02-Sep-15, 03-Sep-15, 04-Sep-15 and 05-Sep-15 exist.

You are giving the folder path in code as given here.

FolderPath1 = "C:\Log 1" ‘Actual path may vary

FolderPath2 = "C:\Log 2"

Date1 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath1)

Date2 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath2)

You could either use Date1 only or Date2 only or use both dates (Date1 and Date2).

If you want to test dates from only one folder, say Log 1, you would comment Date2 line or vice versa.

So we have three options – Log 1 only, Log 2 only or both (Log 1 and Log 2 files).

See the attached word file for all the three options.

Since Log 2 files has only one cycle date ‘01-Sep-15’, for cycle dates other than Sep 1, it is showing not found message.

In Sheet2, you are getting overflow error which has been rectified.

Check the new version which is attached.

Surendran

 

[mshome]

In Log 2 folder files, only Cycle Date 01-Sep-15 exist.

In Log 1 folder files, Cycle Dates 01-Sep-15, 02-Sep-15, 03-Sep-15, 04-Sep-15 and 05-Sep-15 exist.

You are giving the folder path in code as given here.

FolderPath1 = "C:\Log 1" ‘Actual path may vary

FolderPath2 = "C:\Log 2"

Date1 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath1)

Date2 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath2)

You could either use Date1 only or Date2 only or use both dates (Date1 and Date2).

If you want to test dates from only one folder, say Log 1, you would comment Date2 line or vice versa.

So we have three options – Log 1 only, Log 2 only or both (Log 1 and Log 2 files).

See the attached word file for all the three options.

Since Log 2 files has only one cycle date ‘01-Sep-15’, for cycle dates other than Sep 1, it is showing not found message.

In Sheet2, you are getting overflow error which has been rectified.

Check the new version which is attached.

Surendran

its always both folders
it seems working like charm at home
will test this at work on Thursday
i can't thank you enough mr.Surendran
Thank you.

 

[mshome]

i tested it at work
works perfectly thanks dear surendran .