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.

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.

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

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