Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Sum cells in a variable raw using VBA?
Summing Cells in a Variable Raw
I am trying to Sum up cell contents in a variable raw, meaning cells in column N are to be added, but the no of raws may vary. I want to specify Two conditions to Sum these cells , for example if cell D10="", then Sum all cells between N4 and N9, assuming that cells N1 up to N3 have already been Summed up by the macro, otherwise the cell value in N10 is the Product of L10*M10. Any Suggestions, Please. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Sum cells in a variable raw using VBA?
Is N10 just an example, this is where the variability might lie?
To get the last row cLastRow = Cells(Rows.Count,"N").End(xlUp).Row To sum them theSum = WorksheetFunction.Sum(Range("N4:N" & CLastRow) To test D10 With Range("N" & cLastRow+1) if Range("D10").Value = "" Then .Value = WorksheetFunction.Sum(Range("N4:N" & CLastRow) Else .Value = Range("L" & cLastRow+1).Value + Range("M" & cLastRow+1).Value End If End With -- HTH RP (remove nothere from the email address if mailing direct) "IJQ" wrote in message ... Summing Cells in a Variable Raw I am trying to Sum up cell contents in a variable raw, meaning cells in column N are to be added, but the no of raws may vary. I want to specify Two conditions to Sum these cells , for example if cell D10="", then Sum all cells between N4 and N9, assuming that cells N1 up to N3 have already been Summed up by the macro, otherwise the cell value in N10 is the Product of L10*M10. Any Suggestions, Please. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Sum cells in a variable raw using VBA?
Bob,
There's an undocumented difference in the syntax for calling worksheetfunctions in VBA This is the official documented syntax: application.worksheetfunction.MATCH(value,array,ty pe) In xl97 dropping the application qualifier will give a Compile error, but in newer versions works similar to the 'official' syntax. worksheetfunction.MATCH(value,array,type) However dropping the worksheetfunction qualifier works in all versions and has a significant advantage: it will not generate a runtime error if the function returns an errorvalue like #NA application.MATCH(value,array,type) Thus you can eliminate the ON ERROR RESUME NEXT lines when you code for a function that may not yield a valid result. The minor drawback is that you wont have intellisense and autocompletion. I see below you prefer the second syntax. I (respectfully :) suggest you switch to the third. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : Is N10 just an example, this is where the variability might lie? To get the last row cLastRow = Cells(Rows.Count,"N").End(xlUp).Row To sum them theSum = WorksheetFunction.Sum(Range("N4:N" & CLastRow) To test D10 With Range("N" & cLastRow+1) if Range("D10").Value = "" Then .Value = WorksheetFunction.Sum(Range("N4:N" & CLastRow) Else .Value = Range("L" & cLastRow+1).Value + Range("M" & cLastRow+1).Value End If End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Sum cells in a variable raw using VBA?
Just to add
Application.Match really isn't undocumented. That was the only way to do it in xl5/95. The worksheetfunction object wasn't introduced until xl97. Welcome Back. In case you are not awa http://groups.google.co.uk/groups?q=...TNGP14.phx.gbl -- Regards, Tom Ogilvy "keepITcool" wrote in message ft.com... Bob, There's an undocumented difference in the syntax for calling worksheetfunctions in VBA This is the official documented syntax: application.worksheetfunction.MATCH(value,array,ty pe) In xl97 dropping the application qualifier will give a Compile error, but in newer versions works similar to the 'official' syntax. worksheetfunction.MATCH(value,array,type) However dropping the worksheetfunction qualifier works in all versions and has a significant advantage: it will not generate a runtime error if the function returns an errorvalue like #NA application.MATCH(value,array,type) Thus you can eliminate the ON ERROR RESUME NEXT lines when you code for a function that may not yield a valid result. The minor drawback is that you wont have intellisense and autocompletion. I see below you prefer the second syntax. I (respectfully :) suggest you switch to the third. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : Is N10 just an example, this is where the variability might lie? To get the last row cLastRow = Cells(Rows.Count,"N").End(xlUp).Row To sum them theSum = WorksheetFunction.Sum(Range("N4:N" & CLastRow) To test D10 With Range("N" & cLastRow+1) if Range("D10").Value = "" Then .Value = WorksheetFunction.Sum(Range("N4:N" & CLastRow) Else .Value = Range("L" & cLastRow+1).Value + Range("M" & cLastRow+1).Value End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
Cells variable | Excel Programming | |||
Sum Cells and assign to variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Finding cells and summing variable cells | Excel Programming |