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,

    ActiveSheet.Range("M2").Select
    Do Until IsEmpty(ActiveCell.Offset(, -1))
        ActiveCell = Format(ActiveCell, "[$-409]h:mm AM/PM;@")
        ' Step down 1 row from present location.
        ActiveCell.Offset(1, 0).Select
    Loop

isn't a good idea.

You should avoid using ActiveSheet and looping.

  With sourceWorkbook.Sheets(1) ' it's a CSV so only had one sheet.
    .Range("M2", .Range("M" & Rows.Count).NumberFormat = [$-409]h:mm AM/PM;@"
  End With

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.

Else ' step 2 - b/c InDate.source = OutDate.source on same row is false, goto step 2a
                ' step 2a - test if OutDate.source is a valid date
                If IsDate(sourceInDate.Offset(0, 9).Value) = False Then ' if NOT valid date
                    MsgBox "OutDate is NOT valid date" ' DELETE temporary msg when done testing
                    ' Call outNote (placeholder)
 '                   
                    '[outNote]
                    If sourceInDate.Offset(0, 15).Value <> "" Then
                        If sourceInDate.Offset(0, 15).Value = "V" Or sourceInDate.Offset(0, 15).Value = "v" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "S" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "P" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "F" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "H" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "L" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 18).Value * -1 ' copy time in adjLunch.source to OTadjHours.target
                        End If
                    End If
                    '[/outNote]

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.

 ' step 2 - does InDate.source (sourceSheet.Range("K2").Value) = OutDate.source on same row (sourceSheet.Range("T2").Value)
 If sourceInDate.Value = sourceInDate.Offset(0, 9).Value Then  ' if match goto step 3

Ive red bolded same code below

For Each targetDate In targetRange ' selects first date in range in target ws
    For Each sourceInDate In sourceRange ' selects first date in range in source ws
        ' step 1 - does InDate.source (sourceSheet.Range("K2").Value) = Date.target (targetSheet.Range("A14").Value)
        If sourceInDate.Value = targetDate.Value Then ' if match goto step 2
            ' step 2 - does InDate.source (sourceSheet.Range("K2").Value) = OutDate.source on same row (sourceSheet.Range("T2").Value)
            If sourceInDate.Value = sourceInDate.Offset(0, 9).Value Then  ' if match goto step 3                ' step 3 - does InDate.source = PreviousRow.InDate.source
                If sourceInDate.Value <> sourceInDate.Offset(-1, 0).Value Then ' if true, meaning NOT equal, goto step 3a
                    ' step 3a - compare OutTime.source (sourceInDate.Offset(0, 12).Value) > InTime.source (sourceInDate.Offset(0, 3).Value)
                    If sourceInDate.Offset(0, 12).Value > sourceInDate.Offset(0, 3).Value Then ' if true
                        ' copy same row InTime.source to InTime.target
                        targetDate.Offset(0, 1).Value = sourceInDate.Offset(0, 3).Value
                        ' copy OutTime.source to LunchOut.target
                        targetDate.Offset(0, 3).Value = sourceInDate.Offset(0, 12).Value
                        ' Call outNote (placeholder)
                    Else ' step 3a - because OutTime.source > InTime.source is false
                        ' Call outNote (placeholder)
                    End If ' step 3a
                Else ' step 3 - b/c InDate.source = PreviousRow.InDate.source is false, meaning they ARE equal, goto step 3b
                    ' step 3b - compare OutTime.source > InTime.source
                    If sourceInDate.Offset(0, 12).Value > sourceInDate.Offset(0, 3).Value Then ' if true goto step 3b1
                        ' step 3b1 - compare CurrentRow.InTime.source > PreviousRow.OutTime.source
                        If sourceInDate.Offset(0, 3).Value > sourceInDate.Offset(-1, 12).Value Then ' if true
                            ' copy same row InTime.source to LunchIn.target
                            targetDate.Offset(0, 5).Value = sourceInDate.Offset(0, 3).Value
                            ' copy OutTime.source to TimeOut.target
                            targetDate.Offset(0, 7).Value = sourceInDate.Offset(0, 12).Value
                            ' Call outNote (placeholder)
                        End If ' step 3b1
                        ' step 3b2 - does CurrentRow.InTime.source = PreviousRow.outTime.source
                        If sourceInDate.Offset(0, 3).Value = sourceInDate.Offset(-1, 12).Value Then ' if true
                            ' copy OutTime.source to LunchOut.target (replacing previous LunchOut value)
                            targetDate.Offset(0, 3).Value = sourceInDate.Offset(0, 12).Value
                            ' Call outNote (placeholder)
                        End If ' step 3b2
                    End If ' step 3b
                End If ' step 3
            Else ' step 2 - b/c InDate.source = OutDate.source on same row is false, goto step 2a
                ' step 2a - test if OutDate.source is a valid date
                If IsDate(sourceInDate.Offset(0, 9).Value) = False Then ' if NOT valid date
                    MsgBox "OutDate is NOT valid date" ' DELETE temporary msg when done testing
                    ' Call outNote (placeholder)
 '                   
                    '[outNote]
                    If sourceInDate.Offset(0, 15).Value <> "" Then
                        If sourceInDate.Offset(0, 15).Value = "V" Or sourceInDate.Offset(0, 15).Value = "v" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "S" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "P" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "F" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "H" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "L" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 18).Value * -1 ' copy time in adjLunch.source to OTadjHours.target
                        End If
                    End If
                    '[/outNote]
'                    
                End If ' step 2a
            End If ' step 2
        End If ' step 1
    Next sourceInDate ' move down to check the next date in date.source column
Next targetDate ' move down to check the next date in date.target column

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.

            If sourceInDate.Value = targetDate.Value Then    ' if match goto step 2

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,

                 If sourceInDate.Offset(0, 15).Value <> "" Then
                        If sourceInDate.Offset(0, 15).Value = "V" Or sourceInDate.Offset(0, 15).Value = "v" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "S" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "P" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "F" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "H" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value ' copy time in adjHours.source to OTadjHours.target
                        ElseIf sourceInDate.Offset(0, 15).Value = "L" Then
                            targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value ' copy letter in outNote.source to targetType.target
                            targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 18).Value * -1 ' copy time in adjLunch.source to OTadjHours.target
                        End If
                    End If

can probably be rewritten something like this.

            If sourceInDate.Offset(0, 15).Value <> "" Then
               Select Case UCase(sourceInDate.Offset(0, 15).Value)
                   Case "V", "S", "F", "H"
                        targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value    ' copy letter in outNote.source to targetType.target
                        targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 20).Value    ' copy time in adjHours.source to OTadjHours.target
                   Case "L"
                        targetDate.Offset(0, 11).Value = sourceInDate.Offset(0, 15).Value    ' copy letter in outNote.source to targetType.target
                        targetDate.Offset(0, 12).Value = sourceInDate.Offset(0, 18).Value * -1    ' copy time in adjLunch.source to OTadjHours.target
                End Select
             End If

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?

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