Macro Calculation Issues

  • Thread starter Thread starter mastermind
  • Start date Start date
M

mastermind

I have written the code bellow, but for some reason the calculation at
the heart of the code keeps failing. Can anyone tell me why, or
better yet how to fix this problem. Thank you.

Sub Summary()
Set DescriptionTotals = Worksheets("Data
Sheet").Range("D11:D50,J11:J50,D54:D353")
Set QuantityTotals = Worksheets("Data
Sheet").Range("C11:C50,I11:I50,E54:E353")

QuantityTotals = 0

For Each ws In ActiveWorkbook.Sheets
If ws.Name Like "Report *" Then
Set WorksheetDescription =
ws.Range("B17:K31,AE17:AS31,B35:V54")
Set WorksheetQuantity =
ws.Range("BJ17:BJ31,AT17:AX31,W35:AB54")
ind1 = 0
For Each c1 In WorksheetDescription
ind1 = ind1 + 1
If c1.Value > "" Then
ind2 = 0
For Each c2 In DescriptionTotals
ind2 = ind2 + 1
If c2.Value <> "" Then
If c1.Value = c2.Value Then
QuantityTotals.Cells(ind2).Value = _
QuantityTotals.Cells(ind2).Value + _
WorksheetQuantity.Cells(ind1).Value
End If
End If
Next c2
End If
Next c1
End If
Next ws
End Sub
 
Observations and recommendations...

Use Option Explicit.
Declare all variables.
Specify whether you are looping thru Columns, Rows, Areas or Cells.
A range declared containing separate ranges must treat each range separately.

When making a post such as yours, tell us what results you expected
and what results you actually got. "keeps failing" is not much help.

The following code is a start. "QuantityTotals" will need more work
and possibly another loop. It wasn't apparent to me how to assign values.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub Summary_R1()
Dim WorksheetDescription As Excel.Range
Dim DescriptionTotals As Excel.Range
Dim WorksheetQuantity As Excel.Range
Dim QuantityTotals As Excel.Range

Dim rngArea1 As Excel.Range
Dim rngArea2 As Excel.Range

Dim c1 As Excel.Range
Dim c2 As Excel.Range
Dim ws As Excel.Worksheet
Dim ind1 As Long
Dim ind2 As Long

Set DescriptionTotals = Worksheets("Data Sheet") _
.Range("D11:D50,J11:J50,D54:D353")
Set QuantityTotals = Worksheets("Data Sheet") _
.Range("C11:C50,I11:I50,E54:E353")

QuantityTotals = 0

For Each ws In ActiveWorkbook.Sheets
If ws.Name Like "Report *" Then
Set WorksheetDescription = _
ws.Range("B17:K31,AE17:AS31,B35:V54")
Set WorksheetQuantity = _
ws.Range("BJ17:BJ31,AT17:AX31,W35:AB54")
ind1 = 0

For Each rngArea1 In WorksheetDescription.Areas
For Each c1 In rngArea1.Cells
ind1 = ind1 + 1
If c1.Value > "" Then
ind2 = 0
For Each rngArea2 In DescriptionTotals.Areas
For Each c2 In rngArea2.Cells
ind2 = ind2 + 1
If c2.Value <> "" Then
If c1.Value = c2.Value Then
QuantityTotals.Cells(ind2).Value = _
QuantityTotals.Cells(ind2).Value + _
WorksheetQuantity.Cells(ind1).Value
End If
End If
Next c2
Next rngArea2
End If
Next c1
Next rngArea1
End If
Next ws
End Sub
'----------


"mastermind"
<[email protected]>
wrote in message
I have written the code bellow, but for some reason the calculation at
the heart of the code keeps failing. Can anyone tell me why, or
better yet how to fix this problem. Thank you.

Sub Summary()
Set DescriptionTotals = Worksheets("Data
Sheet").Range("D11:D50,J11:J50,D54:D353")
Set QuantityTotals = Worksheets("Data
Sheet").Range("C11:C50,I11:I50,E54:E353")
QuantityTotals = 0
For Each ws In ActiveWorkbook.Sheets
If ws.Name Like "Report *" Then
Set WorksheetDescription =
ws.Range("B17:K31,AE17:AS31,B35:V54")
Set WorksheetQuantity =
ws.Range("BJ17:BJ31,AT17:AX31,W35:AB54")
ind1 = 0
For Each c1 In WorksheetDescription
ind1 = ind1 + 1
If c1.Value > "" Then
ind2 = 0
For Each c2 In DescriptionTotals
ind2 = ind2 + 1
If c2.Value <> "" Then
If c1.Value = c2.Value Then
QuantityTotals.Cells(ind2).Value = _
QuantityTotals.Cells(ind2).Value + _
WorksheetQuantity.Cells(ind1).Value
End If
End If
Next c2
End If
Next c1
End If
Next ws
End Sub
 
Back
Top