Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying formulas if certain cells are changed
Hi
I'm trying to have my worksheet perform autoformulas in certain cells as people keep adding lines to it, which blanks out the formula in the correct cell, and then moaning because it doesn't work. Because of this I want to set up a macro so that if they alter/change any details (even if they add a row in the middle) the formula will be carried out. I've got most of the macro nailed down but am having trouble with the SUMIF in vba. (*** section) U4:BL4 is a row of headings (many of which are repeated) A2 is the selection I want look up U3:BL4 is the row with the values I want to sum up The 1st cell which will have this formula is in the fifth row. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:EE")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" Or _ Cells(r, "C").Value < "" Or _ Cells(r, "E").Value < "" Then *** cells(r,"I").formula=SUMIF($U$4:$BL$4,$A$2,U5:BL5) *** End If End If End Sub However, I need the macro to be generic so the row number changes with the cell which is selected. Something like below cells(r,"I").formula=SUMIF($U$(r-1):$BL$(r-1),$A$2,Ur:BLr) Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying formulas if certain cells are changed
Instead of using .formula, write your formula in R1C1 Reference style.
Open excel and write your formula (in A1 reference style) tools|options|general tab|check R1C1 reference style) steal the formula from the formula bar change the setting back And modify your code so that you use: cells(r,"I").formulaR1C1 = "=yourstolenformulahere" raphiel2063 wrote: Hi I'm trying to have my worksheet perform autoformulas in certain cells as people keep adding lines to it, which blanks out the formula in the correct cell, and then moaning because it doesn't work. Because of this I want to set up a macro so that if they alter/change any details (even if they add a row in the middle) the formula will be carried out. I've got most of the macro nailed down but am having trouble with the SUMIF in vba. (*** section) U4:BL4 is a row of headings (many of which are repeated) A2 is the selection I want look up U3:BL4 is the row with the values I want to sum up The 1st cell which will have this formula is in the fifth row. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:EE")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" Or _ Cells(r, "C").Value < "" Or _ Cells(r, "E").Value < "" Then *** cells(r,"I").formula=SUMIF($U$4:$BL$4,$A$2,U5:BL5) *** End If End If End Sub However, I need the macro to be generic so the row number changes with the cell which is selected. Something like below cells(r,"I").formula=SUMIF($U$(r-1):$BL$(r-1),$A$2,Ur:BLr) Any ideas? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying formulas if certain cells are changed
Cheers for that. I'm still struggling though as the macro appears to be
contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:EE")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" Or _ Cells(r, "C").Value < "" Or _ Cells(r, "E").Value < "" Then ' The below equation is the original sumif formula I was using in the cell ' =SUMIF($U$4:$BL$4,$A$2,U5:BL5) ' the below perform sumif's on the same range Cells(r, "I").FormulaR1C1 = "=SUMIF(R4C21:R4C64,R2C1,RC[12]:RC[55])" Cells(r, "J").FormulaR1C1 = "=SUMIF(R4C21:R4C64,R3C1,RC[11]:RC[54])" Cells(r, "K").FormulaR1C1 = "=SUMIF(R4C21:R4C64,R4C1,RC[10]:RC[53])" ' this totals the sumif's Cells(r, "L").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])+SUM(RC[5]:RC[7])" ' These take the result of the above sumif and multiply it by a unit price Cells(r, "M").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-5]" Cells(r, "N").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-6]" Cells(r, "O").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-7]" ' This gives a grand total of the above three sub-totals Cells(r, "P").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" End If End If End Sub "Dave Peterson" wrote: Instead of using .formula, write your formula in R1C1 Reference style. Open excel and write your formula (in A1 reference style) tools|options|general tab|check R1C1 reference style) steal the formula from the formula bar change the setting back And modify your code so that you use: cells(r,"I").formulaR1C1 = "=yourstolenformulahere" raphiel2063 wrote: Hi I'm trying to have my worksheet perform autoformulas in certain cells as people keep adding lines to it, which blanks out the formula in the correct cell, and then moaning because it doesn't work. Because of this I want to set up a macro so that if they alter/change any details (even if they add a row in the middle) the formula will be carried out. I've got most of the macro nailed down but am having trouble with the SUMIF in vba. (*** section) U4:BL4 is a row of headings (many of which are repeated) A2 is the selection I want look up U3:BL4 is the row with the values I want to sum up The 1st cell which will have this formula is in the fifth row. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:EE")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" Or _ Cells(r, "C").Value < "" Or _ Cells(r, "E").Value < "" Then *** cells(r,"I").formula=SUMIF($U$4:$BL$4,$A$2,U5:BL5) *** End If End If End Sub However, I need the macro to be generic so the row number changes with the cell which is selected. Something like below cells(r,"I").formula=SUMIF($U$(r-1):$BL$(r-1),$A$2,Ur:BLr) Any ideas? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying Formulas to a Range in a Worksheet | Excel Discussion (Misc queries) | |||
applying formulas for all sheets selected | Excel Worksheet Functions | |||
Applying formulas to filtered data | Excel Discussion (Misc queries) | |||
Applying Formulas to Visible Cells Only | Excel Discussion (Misc queries) | |||
Applying names to ranges of cells for formulas | Excel Programming |