VBA problem

[Guest]

My question is: In the first part of this code I set c, d, and e equal to
the S8, T8 and U8 ranges respectively. However, when I use them in my
sumproduct formula at the bottom of this code, they return P8 for c, Q8 for
d, and R8 for u. What's the deal? Thanks!!!

Sub MarkB()
Dim m As Long
Dim n As Long
Dim o As Long
Dim p As Long
Dim q As Long
Dim r As Long
Dim s As Long
Dim x As String
Dim w As String
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range

Set a = ActiveSheet.Range("I8")
Set b = Cells.Find("TOTAL", , xlValues).Offset(-1, 8)
Set c = ActiveSheet.Range("S8")
Set d = ActiveSheet.Range("T8")
Set e = ActiveSheet.Range("U8")

For counter = 1 To (Worksheets.Count - 3)

Range("I7").Activate
Do Until ActiveCell.Offset(1, -7).Value = "TOTAL"
m = ActiveCell.Offset(1, 0).Value
n = ActiveCell.Offset(1, -3).Value
w = ActiveCell.Offset(1, -7).Value
x = ActiveCell.Offset(1, -6).Value
With ActiveCell.Offset(1, 0)
.Value = mark2(m, n)
.Activate
End With
With ActiveCell.Offset(0, 12)
.Value = mark2(m, n)
End With
With ActiveCell.Offset(0, 10)
.Value = w
End With
With ActiveCell.Offset(0, 11)
.Value = x
End With
Loop

Range("J7").Activate
Do Until ActiveCell.Offset(1, -8).Value = "TOTAL"
o = ActiveCell.Offset(1, 0).Value
p = ActiveCell.Offset(1, -3).Value
With ActiveCell.Offset(1, 0)
.Value = mark2(o, p)
.Activate
End With
With ActiveCell.Offset(0, 12)
.Value = mark2(o, p)
End With
Loop

Range(a, b).Formula = _
"=SUMPRODUCT(($B8=" & Range(c, b.Offset(0,
6)).Address(external:=True) & ")*($C8=" _
& Range(d, b.Offset(0, 7)).Address(external:=True) & ")*" & Range(e,
b.Offset(0, 8)).Address _
(ColumnAbsolute:=False, external:=True) & ")"

If Not ActiveSheet.Next Is Nothing Then
ActiveSheet.Next.Activate
End If
Next

End Sub

 

[Frank Kabel]

Hi
but what would be your expected outcome?

 

[Guest]

My expected outcome would be a sumproduct formula that only references S8
through S26 in the first part of the formula. b.offset(0,6) refers to S26.
The rest of the equation works the same way.

 

[Myrna Larson]

You set a, b, c, d, and e at the top, when a particular sheet is active. I
don't see that you ever redefine these variables. As you go through the loop,
the active sheet changes.

I'm looking at this line:

Range(a, b).Formula = _
"=SUMPRODUCT(($B8=" & Range(c, b.Offset(0, 6)).Address(external:=True) ....

Let's say you started with Sheet1. Your variables refer to ranges on that
sheet. On the 2nd pass through the loop, the active sheet is Sheet2.

Range, which is unqualified, now refers to Sheet2. So you try to define a
range on Sheet2, the corners of which are a and b -- two cells on Sheet1. That
won't work. Aren't you getting an error here?

Whether that may have anything to do with your problem I don't know.