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!
"ryan" wrote:
> 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" wrote:
>
> > 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!
|
Similar Threads
