Cell Address References won't update when Sorting

[Fishnerd]

I'm writing a macro for Excel 2003 that loops through my database comparing
all rows to check if a statement is true, and if it is, it adds the cell
address reference of Cell(y, r) to the current formula of Cell(x, qr), which
gives the Sum of all values of Cells(y, r), where that statement is true.
This way, if Cells(y, r) changes later, Cell(x, qr) will automatically change
as well. The end result is each cell in column qr will have formulas that
look like =SUM(A37,A54,A68)

The reference addresses in the formula change correctly when I insert or
delete rows & colums, but my problem is... everytime I sort the database, the
formulas don't update to reflect the new positioning of the referenced cells
after the sort, resulting in completely messed up results until I run the
macro again, whether I use absolute values or not.

Is there an easy fix I'm overlooking? Or a different way entirely to do
this that will cause the cells to stay correctly linked even while sorting?

I appreciate any help or advice you can offer!

My current macro is as follows:

Sub testing()

ActiveSheet.Evaluate("Name").Select
nm = ActiveCell.Column
ActiveSheet.Evaluate("Quantity").Select
r = ActiveCell.Column
ActiveSheet.Evaluate("Quantity_All_Types").Select
qr = ActiveCell.Column

Dim strFormula As String
Dim lngFormula As Long

Rows("2:2").Select
x = ActiveCell.Row

Do While Cells(x, nm).Value <> ""
Do While Cells(y, nm).Value <> ""
If (Cells(x, nm).Value = Cells(y, nm).Value) Then
If (Cells(x, qr).Formula = "") Then
Cells(x, qr).Formula = "=SUM(" & Cells(y, r).Address(0, 0) & ")"
Else
strFormula = Cells(x, qr).Formula
lngFormula = Len(strFormula)
lngFormula = lngFormula - 1
strFormula = Left(strFormula, lngFormula)
strFormula = strFormula & "," & Cells(y, r).Address(0, 0) & ")"
Cells(x, qr) = strFormula
End If
y = y + 1
Else
y = y + 1
End If
Loop
x = x + 1
y = 2
Loop
End Sub

Thanks again!

 

[ryan]

you don't even need a macro for this, just use an array formula of the nature:

=sum(($A$1:$A$50=A1)*($B$1:$B$50))

where i'm assuming that the 'A' column is your "Name" column (nm), and the
'B' column is your "Quantity" column (r). and my array formula would be
entered in cell 'C1' if we assume that the 'C' column is your
"Quantity_All_Types" column (qr).

 

[Fishnerd]

WOW! That is so simple and elegent a fix that at first glance I assumed you
couldn't possibly have solved my issue, but I stand corrected. It works
wonderfully and is SORT friendly!

Thanks Ryan!