For Each Statement confusion!

[joshisms]

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).

[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.

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...

[joshisms]

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!

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.

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).

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.

[Norie]

Where does that code compare the 2 dates?

[joshisms]

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.

[joshisms]

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

[Norie]

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.

[joshisms]

Are you asking or telling me? I'm not sure I am following you. See attached file, this is what my target workbook looks like after the macro runs. You can see it stops after correctly copying over the "V" and the 7.5 on 7/18 date. But nothing gets copied over after that ....

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.

[joshisms]

Did you rename the source file from ".csv.xls" to ".csv" before running the macro?

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 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.

[Norie]

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.

[joshisms]

That's odd. I wonder why it doesn't go past that point? Runs fine for me. How do you know that it stops there? What happens? Does it populate your target timesheet at all? Do you get a msgbox saying "OutDate is NOT valid date"?

Take a look at the file I uploaded: ExTimeCard (TARGET WB after macro ran).xls When I run it, you can see some output. This is where it stops for me. I am using Excel 2k3 on Win7.

Any ideas what wrong?

‎

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.

[joshisms]

Are you using the modified code you wrote in your first post to me? This could be why it stops .....

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...

[Norie]

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.

[joshisms]

Yep, I already rewrote it as a case, but hadn't changed it yet!

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.

[Norie]

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.

[joshisms]

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.

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?

[joshisms]

I'll do my best, but its written very basic because I didn't know any fancier ways of doing/combining things than I did and though that if I wrote enough comments it would be easier to follow.

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.

[joshisms]

The idea here is to compare the dates in the target worksheet (timecard) with the dates in the source worksheet (data). Is there a better way to compare two dates to see if they match?

To be clear what I wanted to have happen was to take the first date in the target worksheet ("A14") and compare it against the first date in the source worksheet after formatted ("K2"). If they match it is supposed to copy the Rounded Time In and Rounded Time Out times to the target worksheet. Once done it would keep checking the source sheet column of dates ("K") for matches to the first target date, and so on and so forth. Let me know if you want me to explain this any further.

I just thought maybe I am comparing the dates incorrectly, which might be why the code is not moving through this section?

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.

[Norie]

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.

[joshisms]

Sorry for delayed response, have been in school all day past 2 days.
Any idea why it doesn't work after the msgbox? Could it have anything to do with use of offsets or a different cell being the active cell?

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.

I did look into using the find command when originally writing the loop, but haven't since and think its a good idea and would love some direction on how to do this! Would this be using the .find command?

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.

[Norie]

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?

[joshisms]

I did step through it. Very odd since it appears to be moving through all the motions and doing what I would expect with regards to the various letter codes, etc in the source data.

That is just very odd. Why would the code stop working if it worked up to that point? I do not understand this. Its frustrating because it looks like it is behaving/working as it should .....

[Norie]

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

[joshisms]

Norie, a friend just sent me this piece of code. he has helped me out by rewriting everything, but I still need it to do one thing that is missing from the code I wrote ....

can you help me with this last piece? its a bit advanced for me, perhaps you can understand it better than I?

This code copies all the punch in/out times over correctly, but I also need it to copy the LUNCH value or ADJ values when there is an OUTNOTE letter present. The LUNCH and ADJ values need to be copied into the OT/Adjust Hours column in the target timesheet. Do you understand? Could you help?

Please Login or Register  to view this content.

[joshisms]

Thanks Cytop, your help was much appreciated!

[Norie]

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

[cytop]

Just to clear any confusion about this code suddenly 'appearing'..

The OP approached me over a week ago, via pm, on OzGrid asking for help with this after I had assisted with other issues.

I had the same issues with the code as others seem to have had so I re-wrote it as I understood it.

This was sent to the OP, by email, on Thursday last week (Aug 22nd) with the comment "...what I have done, though, is re-write the thing. Obviously this is not going to work 100% for you but it may show another way to accomplish this", OP read the email on his mobile and, it seems, never noticed the attachment.

I have watched the various exchanges over the next few days noting that some of the comments had already been raised by me. On Wednesday last, with no reference to the file I sent, I pm'ed the OP on OzGrid saying I was withdrawing from it and would not take any further part. This prompted a reply last night which, in my opinion, was not complimentary to me. Then to see that "a friend just sent me this piece of code..." is a little much.