Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / March 2008

Tip: Looking for answers? Try searching our database.

Macro to summarize data

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
krc547 - 29 Mar 2008 00:14 GMT
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.
Per Jessen - 29 Mar 2008 20:34 GMT
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
>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
[quoted text clipped - 3 lines]
> place the answer in worksheet "Summary" starting at column "G6" and so on
> until 0 - 18 as been recorded on the summary sheet.
krc547 - 31 Mar 2008 16:48 GMT
Hi Jenssen

The code works excepts it errors out when it gets to

Destination:=Sheets(sumSh).Range("C6").Offset(n, 0)

> Hi
>
[quoted text clipped - 36 lines]
> > 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 - 31 Mar 2008 18:27 GMT
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.

> Hi Jenssen
>
[quoted text clipped - 42 lines]
> > > place the answer in worksheet "Summary" starting at column "G6" and so on
> > > until 0 - 18 as been recorded on the summary sheet.

Signature

Dave Peterson

krc547 - 31 Mar 2008 18:38 GMT
Thanks Dave, I get an error when I run it that says Error 400.

> The problem is linewrap in the newsgroup post.
>
[quoted text clipped - 52 lines]
> > > > place the answer in worksheet "Summary" starting at column "G6" and so on
> > > > until 0 - 18 as been recorded on the summary sheet.
krc547 - 31 Mar 2008 18:42 GMT
Hi Dave,

this errors out now that I fixed the line thing:

Set TargetRange = Sheets(mSh).Range("C6", Range("C65536").End(xlUp))

> The problem is linewrap in the newsgroup post.
>
[quoted text clipped - 52 lines]
> > > > 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 - 31 Mar 2008 19:40 GMT
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

> Hi Dave,
>
[quoted text clipped - 62 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

krc547 - 31 Mar 2008 16:51 GMT
It errors out here:

Destination:=Sheets(sumSh).Range("C6").Offset(n, 0)

> Hi
>
[quoted text clipped - 36 lines]
> > place the answer in worksheet "Summary" starting at column "G6" and so on
> > until 0 - 18 as been recorded on the summary sheet.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.