using a for loop to filter column in temp worksheet to sum and count based on conditions, 3 conditions

T

tricket7

New Member
Joined
Jul 25, 2012
Messages
3
Sample:


Query from dBase file source.xls - moves into a temporary worksheet
SCAC DATE PAID WEIGHT
FEDX 6/1/2012 $50.00 100
SCC1 6/1/2012 $1,500.00 5000
SCC2 6/1/2012 $6500.00 12000
FEDX 7/1/2012 $45.00 50
SCC1 7/2/2012 $1,200.00 4500




Temp.xls moves this data into another temporary worksheet
SCAC MONTH DAY YEAR PAID WEIGHT
FEDX JUNE 1 2012 $50.00 100
SCC1 JUNE 1 2012 $1500.00 5000
SCC2 JUNE 1 2012 $6500.00 12000
FEDX JULY 1 2012 $45.00 50
SCC1 JULY 2 2012 $1,200.00 4500







I need to filter data based on these conditions:
TL is anything over 10000
FEDX is anything in column 1 that = FEDX
LTL is anything in column 1 <> FEDX and in column 6 < 10000


I want to loop through the data and temporarily delete rows meeting these conditions so that I can both count the number of rows, and get a total for each month.


This is what the snippet of code looks like: I know the problem lies here:
Code: 
If Load = “TL”  then
Range(“A1”).Select
Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 6).Value < 10000 Then
                Rows(x & ":" & TotalRows).Delete
                Exit For
            End If
        Next x
ElseIf Load = "FEDX" Then
        Range("A2").Select
        Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 1).Value <> "FEDX" Then
                Rows("2:" & x - 1).Delete
                Exit For
            End If
        Next x
Else
        Range("A2").Select
        Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 6).Value < 10000 And Cells(x, 1).Value = "FEDX" Then
                Rows("2:" & x - 1).Delete
                Exit For
            End If
        Next x
    End If


This is the complete program page:
Code: 
'Monthly Traffic Analysis


'**********************************************************************************************
Sub FormatMonthlyTrafficAnalysis(WS As String, Load As String, ShipOrProcess As String)
'**********************************************************************************************
'***** This sub is responsible for pulling the data gathered by the sub-procedure "GetData"
'***** and inserting it into the workbook "Temp.xls" for the Monthly Traffic Analysis report
'**********************************************************************************************
    
    Dim TotalRows As Long
    Dim x As Long
    Dim TargetColumn As Integer
    Dim StartRow As Integer
    Dim DateColumn As String
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    
'===== This is to setup the temporary workbook for traffic analysis information
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'*****Searches for file Temp.xls and deletes it if it exists*****
'*****Then copies the worksheets from the Source.xls workbook*****
'*****and creates a new instance of Temp.xls*****
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If fso.fileexists(myPath & "\Temp.xls") Then
        Kill myPath & "\Temp.xls"
    End If
    Workbooks("Source.xls").Worksheets(WS).Copy
    ActiveSheet.Name = "Temp"
    ActiveWorkbook.SaveAs myPath & "\Temp.xls"
    Workbooks("Temp.xls").Worksheets("Temp").Activate




    '===== Get current total number of rows
    Range("A1").Select
    Selection.End(xlDown).Select
    TotalRows = ActiveCell.Row


 
If Load = "TL" Then
        Range("A2").Select
        Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 6).Value < 10000 Then
                Rows(x & ":" & TotalRows).Delete
                Exit For
            End If
        Next x
ElseIf Load = "FEDX" Then
        Range("A2").Select
        Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 1).Value <> "FEDX" Then
                Rows("2:" & x - 1).Delete
                Exit For
            End If
        Next x
Else
        Range("A2").Select
        Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
        For x = 2 To TotalRows
            If Cells(x, 6).Value < 10000 And Cells(x, 1).Value = "FEDX" Then
                Rows("2:" & x - 1).Delete
                Exit For
            End If
        Next x
    End If
    
    


    ActiveSheet.AutoFilterMode = False
    
'===== Get current total number of rows again since many were just deleted
    Range("A1").Select
    Selection.End(xlDown).Select
    TotalRows = ActiveCell.Row
    
'===== Determines which date column to use based on the ShipOrProcess value passed to this sub
    If ShipOrProcess = "Ship" Then
        DateColumn = "E"
    Else
        DateColumn = "F"
    End If
    
'===== This separates the dates into separate columns so that each month can be broken down on
'===== the report
    Columns(DateColumn & ":" & DateColumn).Select
    Selection.Insert shift:=xlShiftToRight
    Columns(DateColumn & ":" & DateColumn).Select
    Selection.Insert shift:=xlShiftToRight
    Columns(DateColumn & ":" & DateColumn).Select
    Selection.Insert shift:=xlShiftToRight
    
    Columns(DateColumn & ":" & Chr(Asc(DateColumn) + 2)).Select
    Selection.NumberFormat = "general"
    Cells(1, Asc(DateColumn) - 64).Value = "Month"
    Cells(1, Asc(DateColumn) - 63).Value = "Day"
    Cells(1, Asc(DateColumn) - 62).Value = "Year"
    
    Range(Chr(Asc(DateColumn) - 1) & "2:" & Chr(Asc(DateColumn) - 1) & TotalRows).Select
    Selection.TextToColumns Destination:=Range(DateColumn & "2:" & DateColumn & TotalRows), _
        Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1))


'===== This determines where the data will be put into the Monthly Traffic Analysis Report
    If Load = "LTL" Then
        StartRow = 2
'    End If
   ElseIf Load = "FEDX" Then
        StartRow = 34
    ElseIf Load = "TL" Then
        StartRow = 18
    End If
    End If
    
        


    
'==============================================================================================
'===== This section is to extract the number of shipments for the given or previous year for
'===== LTL,TL, or FEDX for each month
'==============================================================================================
    Range("A2").Select
    Selection.Sort key1:=Range(DateColumn & "1"), Header:=xlYes
    Selection.Subtotal groupby:=Asc(DateColumn) - 64, Function:=xlCount, totallist:=2, Replace:=True
    Select Case WS
        Case "SourceCurYTDMTA"
            TargetColumn = 9
        Case "SourcePrevYTDMTA"
            TargetColumn = 3
        Case Else
            MsgBox "There is a problem in the ""FormatMonthlyTrafficAnalysis"" sub, " & _
                    "call Chad." & Chr(10) & "The results will be incorrect!", vbOKOnly, _
                    "Unexpected Error!"
    End Select


    For x = 1 To 12
        Call GetTotalsMTA(x, TargetColumn, StartRow, DateColumn, " Count", 2)
    Next x
'==============================================================================================
    
'==============================================================================================
'===== This section is to extract the total weight for the given or previous year for LTL,
'===== TL, or FEDX for each month
'==============================================================================================
    Range("A2").Select
    Selection.RemoveSubtotal
    Selection.Sort key1:=Range(DateColumn & "1"), Header:=xlYes
    Selection.Subtotal groupby:=Asc(DateColumn) - 64, Function:=xlSum, totallist:=9, Replace:=True
    Select Case WS
        Case "SourceCurYTDMTA"
            TargetColumn = 10
        Case "SourcePrevYTDMTA"
            TargetColumn = 41
        Case Else
            MsgBox "There is a problem in the ""FormatMonthlyTrafficAnalysis"" sub, " & _
                    "call Chad." & Chr(10) & "The results will be incorrect!", vbOKOnly, _
                    "Unexpected Error!"
    End Select


    For x = 1 To 12
        Call GetTotalsMTA(x, TargetColumn, StartRow, DateColumn, " Total", 9)
    Next x
'==============================================================================================
    
'==============================================================================================
'===== This section is to extract the total cost for the given or previous year for LTL,
'===== TL, or FEDX for each month
'==============================================================================================
    Range("A2").Select
    Selection.RemoveSubtotal
    Selection.Sort key1:=Range(DateColumn & "1"), Header:=xlYes
    Selection.Subtotal groupby:=Asc(DateColumn) - 64, Function:=xlSum, totallist:=10, Replace:=True
    Select Case WS
        Case "SourceCurYTDMTA"
            TargetColumn = 12
        Case "SourcePrevYTDMTA"
            TargetColumn = 6
        Case Else
            MsgBox "There is a problem in the ""FormatMonthlyTrafficAnalysis"" sub, " & _
                    "call Chad." & Chr(10) & "The results will be incorrect!", vbOKOnly, _
                    "Unexpected Error!"
    End Select


    For x = 1 To 12
        Call GetTotalsMTA(x, TargetColumn, StartRow, DateColumn, " Total", 10)
    Next x
  
    Application.DisplayAlerts = False
    Workbooks("Temp.xls").Close
    Kill myPath & "\Temp.xls"
    Application.DisplayAlerts = True
    
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You must log in or register to reply here.

Similar threads

R
  • Question Question
Macro not working after adding two new items
Replies
5
Views
444
regan_2000
R
C
  • Question Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
394
dermie_72
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
I
  • Solved
Value in userform isnt entered correctly when sent to worksheet
Replies
6
Views
568
ipbr21054
I
J
  • Question Question
I need to rename a Sheet
Replies
1
Views
510
CSmith
CSmith

Forum statistics

Threads
1,223,986
Messages
6,175,789
Members
452,670
Latest member
nogarth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top