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.