Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly sales summary.
what code will give the following results.
Col A to Col c ,the database is updated daily thus the sales figures in Col c are added every day, date wise( col b) and sales man wise(Col a).I want a monthly summary report salesman wise sales done by salesman in a month.If i put month Jan-2009 in E1, salesmanwise sales summary for the month of Jan-09 be displayed from E2:Fn. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly sales summary.
Hi
See if this is what you want: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then Range("F2").Offset(off, 0) = Cells(r.Row, 3).Value off = off + 1 End If End If Next Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... what code will give the following results. Col A to Col c ,the database is updated daily thus the sales figures in Col c are added every day, date wise( col b) and sales man wise(Col a).I want a monthly summary report salesman wise sales done by salesman in a month.If i put month Jan-2009 in E1, salesmanwise sales summary for the month of Jan-09 be displayed from E2:Fn. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly sales summary.
Many thanks,Per,
you are almost very close to the results I need. The output is coming like this; from E2 Raj 100 200 300 75 john 200 290 mary 100 100 700 The output I need is sum of these values salesman wise. Like this from E2 Raj 675 john 490 mary 900 "Per Jessen" wrote: Hi See if this is what you want: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then Range("F2").Offset(off, 0) = Cells(r.Row, 3).Value off = off + 1 End If End If Next Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... what code will give the following results. Col A to Col c ,the database is updated daily thus the sales figures in Col c are added every day, date wise( col b) and sales man wise(Col a).I want a monthly summary report salesman wise sales done by salesman in a month.If i put month Jan-2009 in E1, salesmanwise sales summary for the month of Jan-09 be displayed from E2:Fn. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly sales summary.
Hi TK Raju
This should do it: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Dim SumValue As Double Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then SumValue = SumValue + Cells(r.Row, 3).Value End If End If Next Range("F2").Offset(off, 0) = SumValue off = off + 1 SumValue = 0 Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... Many thanks,Per, you are almost very close to the results I need. The output is coming like this; from E2 Raj 100 200 300 75 john 200 290 mary 100 100 700 The output I need is sum of these values salesman wise. Like this from E2 Raj 675 john 490 mary 900 "Per Jessen" wrote: Hi See if this is what you want: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then Range("F2").Offset(off, 0) = Cells(r.Row, 3).Value off = off + 1 End If End If Next Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... what code will give the following results. Col A to Col c ,the database is updated daily thus the sales figures in Col c are added every day, date wise( col b) and sales man wise(Col a).I want a monthly summary report salesman wise sales done by salesman in a month.If i put month Jan-2009 in E1, salesmanwise sales summary for the month of Jan-09 be displayed from E2:Fn. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly sales summary.
A million thanks Per Jessen.
I am so much thankful,it gave me much relief. Thanks once again for your help. "Per Jessen" wrote: Hi TK Raju This should do it: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Dim SumValue As Double Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then SumValue = SumValue + Cells(r.Row, 3).Value End If End If Next Range("F2").Offset(off, 0) = SumValue off = off + 1 SumValue = 0 Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... Many thanks,Per, you are almost very close to the results I need. The output is coming like this; from E2 Raj 100 200 300 75 john 200 290 mary 100 100 700 The output I need is sum of these values salesman wise. Like this from E2 Raj 675 john 490 mary 900 "Per Jessen" wrote: Hi See if this is what you want: Sub Report() Dim TargetMonth As Integer Dim SalesMan() As String Dim FilterRange As Range Dim TargetRange As Range Dim c As Long Dim off As Long Application.ScreenUpdating = False Range("E2", Range("F2").End(xlDown)).ClearContents TargetMonth = Month(Range("E1").Value) Set FilterRange = Range("A1", Range("A" & Rows.Count).End(xlUp)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) ReDim SalesMan(TargetRange.Cells.Count - 1) For Each cell In TargetRange SalesMan(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(SalesMan) Range("E2").Offset(off, 0).Value = SalesMan(c) FilterRange.AutoFilter Field:=1, Criteria1:=SalesMan(c) Set TargetRange = FilterRange.SpecialCells(xlCellTypeVisible) FilterRange.AutoFilter For Each r In TargetRange.Rows If r.Row 1 Then If Month(Cells(r.Row, 2).Value) = TargetMonth Then Range("F2").Offset(off, 0) = Cells(r.Row, 3).Value off = off + 1 End If End If Next Next Application.ScreenUpdating = True End Sub Regards, Per "TUNGANA KURMA RAJU" skrev i meddelelsen ... what code will give the following results. Col A to Col c ,the database is updated daily thus the sales figures in Col c are added every day, date wise( col b) and sales man wise(Col a).I want a monthly summary report salesman wise sales done by salesman in a month.If i put month Jan-2009 in E1, salesmanwise sales summary for the month of Jan-09 be displayed from E2:Fn. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to chart monthly sales for successive years | Charts and Charting in Excel | |||
Summary USA sales | Excel Discussion (Misc queries) | |||
show daily sales as a percentage of monthly sales target | Excel Programming | |||
Help monthly sales by person | New Users to Excel | |||
Formula help in a monthly sales report. | Excel Worksheet Functions |