Results 1 to 3 of 3

Problem with loop

Threaded View

walid66 Problem with loop 06-14-2008, 11:41 PM
mudraker The problem with your macro... 06-15-2008, 12:58 AM
walid66 Thanks for your help. It... 06-15-2008, 08:41 PM
  1. #1
    Forum Contributor
    Join Date
    12-09-2006
    Posts
    229

    Problem with loop

    I have this code it works fine but when I try to run the macro again the data does not copy to second line it replaces see attached file.
    Basically I want the macro to run, sum and average the data on monthly report tab and copy the last row to final result tab. If row two has data on the final result tab then it should paste the data into the third row on the second run so on.

    Thanks

    Sub FindFinalResult()
    Application.ScreenUpdating = False
      Dim LastCol As Long
      Dim Rng As Range
      Dim RowNo As Long
      Dim Found As Range
      i = 2
                While Worksheets("Monthly_Report").Cells(i, 1) <> ""
                 
                 i = i + 1
                Wend
                   If Worksheets("Monthly_Report").Cells((i - 1), 1) = Date Then
                    GoTo line1
                   End If
        
                i = 2
                Do While Worksheets("Monthly_Report").Cells(i, 1) <> ""
                    Totla_Seizures = Totla_Seizures + Worksheets("Monthly_Report").Cells(i, 2)
                    NoBlock_Call = NoBlock_Call + Worksheets("Monthly_Report").Cells(i, 3)
                    PerBlock_Call = Application.WorksheetFunction.Average(Worksheets("Monthly_Report").Cells(i, 4))
                    NoSuccess_Call = NoSuccess_Call + Worksheets("Monthly_Report").Cells(i, 5)
                    PerSuccess_Call = Application.WorksheetFunction.Average(Worksheets("Monthly_Report").Cells(i, 6))
                    NoDropped_Call = Application.WorksheetFunction.Average(Worksheets("Monthly_Report").Cells(i, 7))
                    PerDropped_Call = PerDropped_Call + Worksheets("Monthly_Report").Cells(i, 8)
                    Daily_MOU = Daily_MOU + Worksheets("Monthly_Report").Cells(i, 9)
                    
                i = i + 1
                Loop
    
                    Worksheets("Monthly_Report").Cells(i, 2) = Totla_Seizures
                    Worksheets("Monthly_Report").Cells(i, 3) = NoBlock_Call
                    Worksheets("Monthly_Report").Cells(i, 4) = PerBlock_Call
                    Worksheets("Monthly_Report").Cells(i, 5) = NoSuccess_Call
                    Worksheets("Monthly_Report").Cells(i, 6) = PerSuccess_Call
                    Worksheets("Monthly_Report").Cells(i, 7) = NoDropped_Call
                    Worksheets("Monthly_Report").Cells(i, 8) = PerDropped_Call
                    Worksheets("Monthly_Report").Cells(i, 9) = Daily_MOU
                    Worksheets("Monthly_Report").Cells(i, 1) = Date
                    Worksheets("Monthly_Report").Cells(i, 1).Select
                    Range(Selection, Selection.End(xlToRight)).Select
                    Selection.Copy
                        Worksheets("Final_Report").Activate
                        j = 2
     
                                    Cells(j, 1).Select
                                    ActiveSheet.Paste
    line1:
    End Sub
    Attached Files Attached Files

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1