Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula problem
Hi,
After several attempts, it fails to show the correct result for the following formula Extract of vba code Cells(s, 3).Formula = "=SUM(" & Range(Cells(s, 3).Offset(-1, 0), _ Cells(s, 3).End(xlUp)).Address(False, False) & ")" & "- SUMIF(C[-2],""CF Total"",C)" Any idea ? thanks in advance Regards Len |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula problem
Check the formula on the worksheet to see if it is correct. I think your code could of been producing a formula which would cause a circular calculation. It depends on the data in the worksheet. the code below is much easier to debug. Set FirstCell = Cells(s - 1, "C").end(xlup) Set LastCell = Cells(s - 1, "C") Set SumRange = Range(FirstCell,LastCell) Set ColA_Range = _ Range(Range("A" & FirstCell.Row),Range("A" & LastCell.Row)) Cells(s, 3).Formula = "=SUM(" & Sumrange.Address & ")-" & _ "SUMIF(" ColA_Range.address &",""CF Total""," & _ SumRange.Address ")" -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181718 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula problem
Hi Joel,
Thanks for your reply and your codes. It works perfectly ! One more question, how to modify your codes so that this formula becomes variable formula which can be copied and paste to next 12 columns at the same row so that it can sum up the 12 months with Total tabulation ? Thanks again Regards Len |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula problem
I posted below thbe help for the ADDRESS property. The default settting is absolute (true) which puts the dollar sign into the formula. You need to to set the property to false. I'm not surre which parts of the formula you want absolute and which parts you don't want absolute so you may have to change the True/False as required. Cells(s, 3).Formula = "=SUM(" & _ Sumrange.Address(RowAbsolute:=False,ColumnAbsolute :=False) & _ ")-" & _ "SUMIF(" & _ ColA_Range.address(RowAbsolute:=False,ColumnAbsolu te:=False) & _ ",""CF Total""," & _ SumRange.Address(RowAbsolute:=False,ColumnAbsolute :=False) & ")" expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo) expression Required. An expression that returns one of the above objects. RowAbsolute Optional Variant. True to return the row part of the reference as an absolute reference. The default value is True. ColumnAbsolute Optional Variant. True to return the column part of the reference as an absolute reference. The default value is True. ReferenceStyle Optional XlReferenceStyle. XlReferenceStyle can be one of these XlReferenceStyle constants. xlA1 default. Use xlA1 to return an A1-style reference. xlR1C1. Use xlR1C1 to return an R1C1-style reference. External Optional Variant. True to return an external reference. False to return a local reference. The default value is False. RelativeTo Optional Variant. If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181718 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula problem
Hi Joel,
Thanks for your great help! Your codes on formula with Address property is very useful and need to work around Regards Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem | Excel Discussion (Misc queries) | |||
I have a problem with my Formula. | Excel Worksheet Functions | |||
Problem with formula =MAX(A1-40,0) | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Programming | |||
problem with formula | Excel Discussion (Misc queries) |