Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address References won't update when Sorting
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address References won't update when Sorting
you don't even need a macro for this, just use an array formula of the natu
=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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address References won't update when Sorting
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 natu =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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
References omit formatting and return cell address - not data | New Users to Excel | |||
Cell references auto update when sorting | Excel Discussion (Misc queries) | |||
External cell references using INDIRECT & ADDRESS | Excel Worksheet Functions | |||
Auto Update Cell (Row) References | Excel Programming | |||
Cell references do not update | Excel Discussion (Misc queries) |