For Each Statement confusion!

xpost: http://www.ozgrid.com/forum/showthre...810#post681810
xpost: http://www.mrexcel.com/forum/excel-q...ml#post3559652

I'm in the middle of finishing a lengthy macro and cannot get my head around how to make this For Each statement work properly. Any brave souls up for a challenge?

-----------------------------------------

Calling all Excel Experts!! Your help is desperately needed!! Will anyone please help me solve this issue?

Visit the link https://app.box.com/s/r4k7nvgzwk7wqq0ciqjj and read the _README file (it is also below!) It will help to understand how this macro is supposed to work. There are screenshots and descriptions clearly explaining the steps. All files can be previewed in your browser and do not require download.

I have re-uploaded new files to this post below (labeled with a "ver2"). The source worksheet is a .csv file saved as .xls because ozgrid does not allow .csv files to be uploaded. You will have to resave the source file from ".csv.xls" to ".csv" when you save it to your hard drive locally to make it work.

The macro is in the target workbook file. Best thing would be to allow all the formatting to run and start stepping through it at the point in the code where the IMPORTING BEGINS with the For Each Statement (near the end of code).

The ExUser3-20130729 (SOURCE WS ver2).csv.xls is not an xls file so can't be opened.

It does open if the extension is changed from .csv.xls to just .xls.

As for the problem, any chance you could tell us what it is? Or do we have to wade through the code to find it ourselves.

Oh, and it might be useful to know how to run the code.

PS This sort of code,

Please Login or Register  to view this content.

isn't a good idea.

You should avoid using ActiveSheet and looping.

Please Login or Register  to view this content.

Note I've changed the code to use NumberFormat as if you use Format you could end up with text values in the cells rather than actual time values, and that might cause problems later if doing any calculations, comparisons, searches...

Hi Norie, Thank you for replying. If you don't mind, please read the _README PDF document for a second. It should explain a lot of questions you might have, but hopefully my explanation below helps too!

Originally Posted by Norie

The ExUser3-20130729 (SOURCE WS ver2).csv.xls is not an xls file so can't be opened. It does open if the extension is changed from .csv.xls to just .xls.

The source worksheet is a .csv file saved as .xls because ozgrid does not allow .csv files to be uploaded. You will have to resave the source file from ".csv.xls" to ".csv" when you save it to your hard drive locally to make it work.

WHAT IT DOES:
The entire macro is designed to import timecard punches from on online system to a workbook that looks like a timecard. the online timecard punches are the source data and the timecard workbook where the data goes is the target.

The source csv file is formatted before any data is copied/imported to the target worksheet. Basically the source file dates/times are rounded and formatted as dates/times before copy/import process begins. the import process is simply copying all punch in/out times based on matching dates between the worksheets.

Originally Posted by Norie

Oh, and it might be useful to know how to run the code.

The macro is in the target workbook file. Alt-8, Edit, Run? Best thing would be to allow all the formatting to run by putting a stop at the point in the code where the IMPORTING BEGINS with the For Each Statement (near the end of all the code).

Originally Posted by Norie

As for the problem...

The problem is that as the data from the csv files is imported into the target workbook, it stops when it encounters no OutDate in the source file. This portion of the code tests whether or not the indate matches the outdate, if they don't match, the outdate is tested to see if it is a valid date. if outdate is not a valid date there is always a letter in one of the cells to the right of the outdate. i want to read what that code is and copy it and the values to its right to the target workbook. I set up a msgbox for testing purposes to let me know when I started receiving errors.

Please Login or Register  to view this content.

Where does that code compare the 2 dates?

step 2 - see below. I tried to tie everything together in the comments because there are so many different If's and Else's but its easy to get them confused. The step name is before the If statement and after the else.

Please Login or Register  to view this content.

Ive red bolded same code below

Please Login or Register  to view this content.

maybe seeing this will help to keep the Ifs/Elses straight? Download it or view as actual size otherwise it will be too small to view! https://app.box.com/s/heph5urz64h7os5we7gh

The code never goes past this point.

Please Login or Register  to view this content.

It steps right to the End If every time, skipping all the code after the above line.

That's what happened when I stepped through the entire code.

Yes I did rename it, otherwise it wouldn't have got past trying to open the file.

It doesn't actually stop there it never exectutes the code within the If End If block.

How did I find that out? I stepped through the entire code.

Can you tell us what exactly is the problem, in words and without uploading more files?

Are you sure the problem is with the For Next?

Is it not possible it could be something in the code within the loops?

By the way, something like this,

Please Login or Register  to view this content.

can probably be rewritten something like this.

Please Login or Register  to view this content.

You should probably do that throughout the code, it makes it easier to follow/understand and that should make it easier to debug and find problems.

PS I don't mean replace everthing with Select Case, just kind of tidy things up.

The entire macro is designed to import timecard punches from on online system to a workbook that looks like a timecard. the online timecard punches are the source data and the timecard workbook where the data goes is the target.

The source csv file is formatted before any data is copied/imported to the target worksheet. Basically the source file dates/times are rounded and formatted as dates/times before copy/import process begins. The import process is simply copying all punch in/out times based on matching dates between the worksheets.

The problem is that ALL the source file punch in and out times should be imported into the target workbook but they arent. It stops after it reads a blank cell in outdate in the source sheet. This first occurs on 7/18/2013. It copies the letter code correctly and the 7.5 afterwards, but no other dates are compared and no punch in/out times are copied to the target file after this.

I dont know why it stops when it encounters no date in the OutDate cell in the source file.

The For Each portion of the code tests whether or not the indate matches the outdate, if they don't match, the outDate is tested to see if it is a valid date. if outDate is not a valid date (or whenever if outDate is blank) there will always be a cell to the right that contains a letter code. what it is supposed to do is copy this letter code and the time value to its right into the target worksheet and then continue hunting for matching dates to continue copying all the timeIns and timeOuts to the target worksheet.

The msgbox is for testing purposes only to let me know when it did not find an outDate and as a way for me to stop the code, nothing more.

Originally Posted by Norie

Can you tell us what exactly is the problem, in words and without uploading more files?
Are you sure the problem is with the For Next?
Is it not possible it could be something in the code within the loops?

The code doesn't stop, it keeps going after the message box.

It might appear to have stopped because it doesn't really do anything from then on.

Try it yourself.

Run the code, when the message box appears hit CTRL+BREAK, then Click Debug and start stepping through with F8.

It just seems to jump from If to If as it continues looping.

By the way, have you considered a different approach.

For example loop through the dates in the time card worksheet and use code to find them in the CSV file.

Find could be one way but there are others, eg Application.Match.

As for the reason it does nothing after the message box that could be something to do with the data.

Or it could be something to do with how the code is handling the data.

Did you check what was happening (or not happening) as you stepped through the code?

Take a close look at the expressions in the If statements and the values used in them.

Probably best if you ask the person who supplied that code.