+ Reply to Thread
Results 1 to 27 of 27

For Each Statement confusion!

Hybrid View

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    CV, CA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: For Each Statement confusion!

    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
    Last edited by joshisms; 08-28-2013 at 05:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] confusion with IF statement
    By Mortada in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-15-2012, 05:48 PM
  2. Confusion with last row
    By LiLi1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-15-2011, 06:09 PM
  3. IF Statement Confusion
    By penny in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2007, 03:38 PM
  4. If statement confusion
    By DianeR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2007, 07:48 AM
  5. [SOLVED] Confusion.....
    By Eric @ CMN, Evansville in forum Excel General
    Replies: 3
    Last Post: 12-27-2005, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1