Macro to summarize data

[krc547]

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]

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]

Hi Jenssen

The code works excepts it errors out when it gets to

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

 

[krc547]

It errors out here:

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

 

[Dave Peterson]

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]

Thanks Dave, I get an error when I run it that says Error 400.

 

[krc547]

Hi Dave,

this errors out now that I fixed the line thing:

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

 

[Dave Peterson]

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