Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
I need a macro to look at worksheet "Mar 08" starting in Column "C6" look
down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
Hi
Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
Hi Jenssen
The code works excepts it errors out when it gets to Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
It errors out he
Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
The problem is linewrap in the newsgroup post.
These two physical lines are actually one logical line: Range(cRange).Copy _ Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) The space character followed by the underscore character means that the line is continued. krc547 wrote: Hi Jenssen The code works excepts it errors out when it gets to Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
Thanks Dave, I get an error when I run it that says Error 400.
"Dave Peterson" wrote: The problem is linewrap in the newsgroup post. These two physical lines are actually one logical line: Range(cRange).Copy _ Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) The space character followed by the underscore character means that the line is continued. krc547 wrote: Hi Jenssen The code works excepts it errors out when it gets to Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
Hi Dave,
this errors out now that I fixed the line thing: Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) "Dave Peterson" wrote: The problem is linewrap in the newsgroup post. These two physical lines are actually one logical line: Range(cRange).Copy _ Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) The space character followed by the underscore character means that the line is continued. krc547 wrote: Hi Jenssen The code works excepts it errors out when it gets to Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to summarize data
I didn't look at the code too closely, but this compiles:
Option Explicit Sub Summary() Dim TargetRange As Range Dim f As Range Dim mSh As String Dim SumSh As String Dim n As Long Dim cRange As String SumSh = "Summary" mSh = "Mar 08" With Sheets(mSh) Set TargetRange = .Range("C6", .Range("C65536").End(xlUp)) End With With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy _ Destination:=Sheets(SumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy _ Destination:=Sheets(SumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub krc547 wrote: Hi Dave, this errors out now that I fixed the line thing: Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) "Dave Peterson" wrote: The problem is linewrap in the newsgroup post. These two physical lines are actually one logical line: Range(cRange).Copy _ Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) The space character followed by the underscore character means that the line is continued. krc547 wrote: Hi Jenssen The code works excepts it errors out when it gets to Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) "Per Jessen" wrote: Hi Here's a start. Sub Summary() Dim TargetRange As Range Dim f sumSh = "Summary" mSh = "Mar 08" Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp)) With TargetRange For n = 0 To 4 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n, 0) End If Next For n = 5 To 8 Set f = .Find(what:=n) If Not f Is Nothing Then cRange = Range(f.Address).Offset(0, 4).Address Range(cRange).Copy Destination:=Sheets(sumSh).Range("C6").Offset(n - 5, 4) End If Next End With End Sub regards, Per "krc547" skrev i meddelelsen ... I need a macro to look at worksheet "Mar 08" starting in Column "C6" look down the column and if there is a 0,1,2,3,4 the return the amount in column "G" to worksheet "Summary" starting in column "C6" fill down, then look at the same worksheet and if there is a 5,6,7,8 then look at column "G6" and place the answer in worksheet "Summary" starting at column "G6" and so on until 0 - 18 as been recorded on the summary sheet. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for a way to summarize data? | Excel Worksheet Functions | |||
How to cause a pivot tables to not summarize data - just report the data | Excel Discussion (Misc queries) | |||
Summarize Data | Excel Discussion (Misc queries) | |||
Summarize data | New Users to Excel | |||
Summarize Data Set | Excel Worksheet Functions |