+ Reply to Thread
Results 1 to 6 of 6

Code loop in Excel 2007

Hybrid View

TPAEm Code loop in Excel 2007 03-15-2010, 07:13 AM
romperstomper Re: Code loop in Excel 2007 03-15-2010, 07:43 AM
TPAEm Re: Code loop in Excel 2007 03-15-2010, 08:00 AM
TPAEm Re: Code loop in Excel 2007 03-15-2010, 08:03 AM
romperstomper Re: Code loop in Excel 2007 03-15-2010, 08:04 AM
TPAEm Re: Code loop in Excel 2007 03-15-2010, 08:48 AM
  1. 03-15-2010, 07:13 AM #1
    TPAEm is offline
    Registered User
    Join Date
    03-15-2010
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Code loop in Excel 2007

    Hi,

    I'm trying to simplify some code by running a loop rather than setting the code to run on each of a number of worksheets within a file. The code works for each worksheet individually and is as follows:

    For m = 1 To jctn

Sheets("J1").Select

If Sheets("Input Data").Cells(20 + (m - 1) * 8, 4) = 0 And Sheets("Input Data").Cells(21 + (m - 1) * 8, 5) = 0 And Sheets("Input Data").Cells(22 + (m - 1) * 8, 4) = 0 Then GoTo Finish Else GoTo Junction1

Junction1:
'insert Friday data into total traffic matrices
    s = 0
    For i = 1 To num_times
    For j = (20 + (s * 8)) To (25 + (s * 8))
    For k = 3 To 9
    Movement_num_in = Sheets("Input Data").Cells(j, k).Value
    Movement_num_out_f = Sheets("Friday Output").Cells(i + 1, 5).Value
    Quarter_f = Sheets("Friday Output").Cells(i + 1, 2).Value
    Total_Flow_f = Sheets("Friday Output").Cells(i + 1, 7).Value
    Total_HGV_f = Sheets("Friday Output").Cells(i + 1, 8).Value
    If Movement_num_in = Movement_num_out_f And Quarter_f = 1 Then
        Cells(j - 14 - (s * 8), k + 3) = Total_Flow_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 2 Then
        Cells(j - 5 - (s * 8), k + 3) = Total_Flow_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 3 Then
        Cells(j + 4 - (s * 8), k + 3) = Total_Flow_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 4 Then
        Cells(j + 13 - (s * 8), k + 3) = Total_Flow_f
    End If
'insert data into HGV matrices
    If Movement_num_in = Movement_num_out_f And Quarter_f = 1 Then
        Cells(j - 14 - (s * 8), k + 14) = Total_HGV_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 2 Then
        Cells(j - 5 - (s * 8), k + 14) = Total_HGV_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 3 Then
        Cells(j + 4 - (s * 8), k + 14) = Total_HGV_f
        ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 4 Then
        Cells(j + 13 - (s * 8), k + 14) = Total_HGV_f
    End If
'insert Saturday data into total traffic matrices
    Movement_num_out_s = Sheets("Saturday Output").Cells(i + 1, 5).Value
    Quarter_s = Sheets("Saturday Output").Cells(i + 1, 2).Value
    Total_Flow_s = Sheets("Saturday Output").Cells(i + 1, 7).Value
    Total_HGV_s = Sheets("Saturday Output").Cells(i + 1, 8).Value
    If Movement_num_in = Movement_num_out_s And Quarter_s = 1 Then
        Cells(j + 35 - (s * 8), k + 3) = Total_Flow_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 2 Then
        Cells(j + 44 - (s * 8), k + 3) = Total_Flow_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 3 Then
        Cells(j + 53 - (s * 8), k + 3) = Total_Flow_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 4 Then
        Cells(j + 62 - (s * 8), k + 3) = Total_Flow_s
    End If
'insert data into HGV matrices
    If Movement_num_in = Movement_num_out_s And Quarter_s = 1 Then
        Cells(j + 35 - (s * 8), k + 14) = Total_HGV_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 2 Then
        Cells(j + 44 - (s * 8), k + 14) = Total_HGV_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 3 Then
        Cells(j + 53 - (s * 8), k + 14) = Total_HGV_s
        ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 4 Then
        Cells(j + 62 - (s * 8), k + 14) = Total_HGV_s
    End If
    
    Next k
    Next j
    Next i

m = m + 1
If m > jctn Then GoTo Finish

    This continues with Sheets("J2").Select, and so on for up to 20 different sheets. This code takes data from the first and second sheets in the file and enters it into specified cells; currently, this method of applying the code to each worksheet works, and gives the correct outputs. I've tried to condense the code by using a loop, shown below:

    m = 1
S = 0

Sheets("J1").Select

Movements:
Do
    If Sheets("Input Data").Cells(26 + (S * 8), 9) = 0 Then GoTo Finish
    For i = 1 To num_times
    For j = (20 + (S * 8)) To (25 + (S * 8))
    For k = 3 To 9
    Movement_num_in = Sheets("Input Data").Cells(j, k).Value 'movement number in input sheet
    Movement_num_out_f = Sheets("Friday Output").Cells(i + 1, 5).Value 'movement number referenced in Friday Output sheet
    Quarter_f = Sheets("Friday Output").Cells(i + 1, 2).Value 'number of quarter-hour segment referenced in Friday Output
    Total_Flow_f = Sheets("Friday Output").Cells(i + 1, 7).Value 'Total flow for associated quarter
    Total_HGV_f = Sheets("Friday Output").Cells(i + 1, 8).Value 'HGV flow for associated quarter
    Movement_num_out_s = Sheets("Saturday Output").Cells(i + 1, 5).Value 'as _f, but Saturday
    Quarter_s = Sheets("Saturday Output").Cells(i + 1, 2).Value 'as _f, but Saturday
    Total_Flow_s = Sheets("Saturday Output").Cells(i + 1, 7).Value 'as _f, but Saturday
    Total_HGV_s = Sheets("Saturday Output").Cells(i + 1, 8).Value 'as _f, but Saturday
        If Movement_num_in = Movement_num_out_f And Quarter_f = 1 Then
            Cells(j - 14 - (S * 8), k + 3) = Total_Flow_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 2 Then
            Cells(j - 5 - (S * 8), k + 3) = Total_Flow_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 3 Then
            Cells(j + 4 - (S * 8), k + 3) = Total_Flow_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 4 Then
            Cells(j + 13 - (S * 8), k + 3) = Total_Flow_f
        End If
'insert data into HGV matrices
        If Movement_num_in = Movement_num_out_f And Quarter_f = 1 Then
            Cells(j - 14 - (S * 8), k + 14) = Total_HGV_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 2 Then
            Cells(j - 5 - (S * 8), k + 14) = Total_HGV_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 3 Then
            Cells(j + 4 - (S * 8), k + 14) = Total_HGV_f
            ElseIf Movement_num_in = Movement_num_out_f And Quarter_f = 4 Then
            Cells(j + 13 - (S * 8), k + 14) = Total_HGV_f
        End If
'insert Saturday data into total traffic matrices
        If Movement_num_in = Movement_num_out_s And Quarter_s = 1 Then
            Cells(j + 35 - (S * 8), k + 3) = Total_Flow_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 2 Then
            Cells(j + 44 - (S * 8), k + 3) = Total_Flow_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 3 Then
            Cells(j + 53 - (S * 8), k + 3) = Total_Flow_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 4 Then
            Cells(j + 62 - (S * 8), k + 3) = Total_Flow_s
        End If
'insert data into HGV matrices
        If Movement_num_in = Movement_num_out_s And Quarter_s = 1 Then
            Cells(j + 35 - (S * 8), k + 14) = Total_HGV_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 2 Then
            Cells(j + 44 - (S * 8), k + 14) = Total_HGV_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 3 Then
            Cells(j + 53 - (S * 8), k + 14) = Total_HGV_s
            ElseIf Movement_num_in = Movement_num_out_s And Quarter_s = 4 Then
            Cells(j + 62 - (S * 8), k + 14) = Total_HGV_s
        End If
        If ActiveSheet.Index = (Sheets.Count - 1) Then
            GoTo Finish
        Else: Sheets(ActiveSheet.Index + 2).Activate
        End If
    Next k
    Next j
    Next i
    S = S + 1
    m = m + 1
Loop Until m = jctn

    But this gives me blank output. I am fairly new to this, although I did write the original code myself.

    Can anyone give me any pointers please?
    Last edited by TPAEm; 03-15-2010 at 08:50 AM.
    Reply With QuoteRegister To Reply
  2. 03-15-2010, 07:43 AM #2
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,017

    Re: Code loop in Excel 2007

    Hi,
    First pointer: avoid Goto statements, except in On Error Goto lines. It makes your code really hard to follow (especially when we only have part of it) and is almost always unnecessary.
    Second pointer: A sample workbook showing your layout (dummy data preferably) so we can see exactly what is happening, together with a description (in plain English) of what you actually want to happen makes our lives a lot easier!
    Third: proper code indentation makes it a lot easier to read, and a few comments explaining what is going on wouldn't go amiss.

    At a rough guess, I think you want something like this:
        Dim wksJunction As Worksheet
    Dim wksInput As Worksheet
    
    Set wksInput = Sheets("Input Data")
    For m = 1 To jctn
    
        Set wksJunction = Sheets("J" & m)
    
        If wksInput.Cells(20 + (m - 1) * 8, 4) = 0 And wksInput.Cells(21 + (m - 1) * 8, 5) = 0 And _
                    wksInput.Cells(22 + (m - 1) * 8, 4) = 0 Then
            ' finished so exit
            Exit For
        Else
            
        'insert Friday data into total traffic matrices
            s = 0
            For i = 1 To num_times
                For j = (20 + (s * 8)) To (25 + (s * 8))
                    For k = 3 To 9
                    
                        Movement_num_in = wksInput.Cells(j, k).Value
                        
                        With Sheets("Friday Output")
                            Movement_num_out_f = .Cells(i + 1, 5).Value
                            Quarter_f = .Cells(i + 1, 2).Value
                            Total_Flow_f = .Cells(i + 1, 7).Value
                            Total_HGV_f = .Cells(i + 1, 8).Value
                        End With
                        
                        If Movement_num_in = Movement_num_out_f Then
                            Select Case Quarter_f
                                Case 1
                                    Cells(j - 14 - (s * 8), k + 3) = Total_Flow_f
                                    Cells(j - 14 - (s * 8), k + 14) = Total_HGV_f
                                Case 2
                                    Cells(j - 5 - (s * 8), k + 3) = Total_Flow_f
                                    Cells(j - 5 - (s * 8), k + 14) = Total_HGV_f
                                Case 3
                                    Cells(j + 4 - (s * 8), k + 3) = Total_Flow_f
                                    Cells(j + 4 - (s * 8), k + 14) = Total_HGV_f
                                Case 4
                                    Cells(j + 13 - (s * 8), k + 3) = Total_Flow_f
                                    Cells(j + 13 - (s * 8), k + 14) = Total_HGV_f
                            End Select
                        End If
                        
                    'insert Saturday data into total traffic matrices
                        With Sheets("Saturday Output")
                            Movement_num_out_s = .Cells(i + 1, 5).Value
                            Quarter_s = .Cells(i + 1, 2).Value
                            Total_Flow_s = .Cells(i + 1, 7).Value
                            Total_HGV_s = .Cells(i + 1, 8).Value
                        End With
                        
                        If Movement_num_in = Movement_num_out_s Then
                            Select Case Quarter_s
                                Case 1
                                    Cells(j + 35 - (s * 8), k + 3) = Total_Flow_s
                                    Cells(j + 35 - (s * 8), k + 14) = Total_HGV_s
                                Case 2
                                    Cells(j + 44 - (s * 8), k + 3) = Total_Flow_s
                                    Cells(j + 44 - (s * 8), k + 14) = Total_HGV_s
                                Case 3
                                    Cells(j + 53 - (s * 8), k + 3) = Total_Flow_s
                                    Cells(j + 53 - (s * 8), k + 14) = Total_HGV_s
                                Case 4
                                    Cells(j + 62 - (s * 8), k + 3) = Total_Flow_s
                                    Cells(j + 62 - (s * 8), k + 14) = Total_HGV_s
                            End Select
                        End If
                    
                    Next k
                    
                Next j
                
            Next i
            
        End If
        
    Next m
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
  3. 03-15-2010, 08:00 AM #3
    TPAEm is offline
    Registered User
    Join Date
    03-15-2010
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Code loop in Excel 2007

    Sorry about that, I'll see if I can trim the document down next time to upload that, it'll make more sense. The spreadsheet itself is complicated, which is why I failed to give you a good idea of what the data does. I'm trying to comment my code more, which would have also been helpful. I'm still getting to grips with the indentation and layout, as this is my first piece of code that hasn't been adapted form someone else's.

    Your suggestion works, but doesn't loop through the worksheets to input the data into each sheet I need. I'll trim and attach a sample file that works with the old code, so you can see what I'm trying to do.
    Reply With QuoteRegister To Reply
  4. 03-15-2010, 08:03 AM #4
    TPAEm is offline
    Registered User
    Join Date
    03-15-2010
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Code loop in Excel 2007

    This is an abridged version of the file that works. At the moment it's designed to work for up to 18 additional worksheets.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-15-2010, 08:04 AM #5
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,017

    Re: Code loop in Excel 2007

    I suspect that's because I forgot an important bit while tidying up:
        Dim wksJunction As Worksheet
    Dim wksInput As Worksheet
    
    Set wksInput = Sheets("Input Data")
    For m = 1 To jctn
    
        Set wksJunction = Sheets("J" & m)
    
        If wksInput.Cells(20 + (m - 1) * 8, 4) = 0 And wksInput.Cells(21 + (m - 1) * 8, 5) = 0 And _
                    wksInput.Cells(22 + (m - 1) * 8, 4) = 0 Then
            ' finished so exit
            Exit For
        Else
            
        'insert Friday data into total traffic matrices
            s = 0
            For i = 1 To num_times
                For j = (20 + (s * 8)) To (25 + (s * 8))
                    For k = 3 To 9
                    
                        Movement_num_in = wksInput.Cells(j, k).Value
                        
                        With Sheets("Friday Output")
                            Movement_num_out_f = .Cells(i + 1, 5).Value
                            Quarter_f = .Cells(i + 1, 2).Value
                            Total_Flow_f = .Cells(i + 1, 7).Value
                            Total_HGV_f = .Cells(i + 1, 8).Value
                        End With
                        
                        If Movement_num_in = Movement_num_out_f Then
                            With wksJunction
                                Select Case Quarter_f
                                    Case 1
                                        .Cells(j - 14 - (s * 8), k + 3) = Total_Flow_f
                                        .Cells(j - 14 - (s * 8), k + 14) = Total_HGV_f
                                    Case 2
                                        .Cells(j - 5 - (s * 8), k + 3) = Total_Flow_f
                                        .Cells(j - 5 - (s * 8), k + 14) = Total_HGV_f
                                    Case 3
                                        .Cells(j + 4 - (s * 8), k + 3) = Total_Flow_f
                                        .Cells(j + 4 - (s * 8), k + 14) = Total_HGV_f
                                    Case 4
                                        .Cells(j + 13 - (s * 8), k + 3) = Total_Flow_f
                                        .Cells(j + 13 - (s * 8), k + 14) = Total_HGV_f
                                End Select
                            End With
                        End If
                        
                    'insert Saturday data into total traffic matrices
                        With Sheets("Saturday Output")
                            Movement_num_out_s = .Cells(i + 1, 5).Value
                            Quarter_s = .Cells(i + 1, 2).Value
                            Total_Flow_s = .Cells(i + 1, 7).Value
                            Total_HGV_s = .Cells(i + 1, 8).Value
                        End With
                        
                        If Movement_num_in = Movement_num_out_s Then
                            With wksJunction
                                Select Case Quarter_s
                                    Case 1
                                        .Cells(j + 35 - (s * 8), k + 3) = Total_Flow_s
                                        .Cells(j + 35 - (s * 8), k + 14) = Total_HGV_s
                                    Case 2
                                        .Cells(j + 44 - (s * 8), k + 3) = Total_Flow_s
                                        .Cells(j + 44 - (s * 8), k + 14) = Total_HGV_s
                                    Case 3
                                        .Cells(j + 53 - (s * 8), k + 3) = Total_Flow_s
                                        .Cells(j + 53 - (s * 8), k + 14) = Total_HGV_s
                                    Case 4
                                        .Cells(j + 62 - (s * 8), k + 3) = Total_Flow_s
                                        .Cells(j + 62 - (s * 8), k + 14) = Total_HGV_s
                                End Select
                            End With
                        End If
                    
                    Next k
                    
                Next j
                
            Next i
            
        End If
        
    Next m
    Reply With QuoteRegister To Reply
  6. 03-15-2010, 08:48 AM #6
    TPAEm is offline
    Registered User
    Join Date
    03-15-2010
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Code loop in Excel 2007

    Thank you, with one minor tweak (S=m-1), it works like a dream!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1