WorksheetFunction

B

Ben

Hi
I am using the function Application.WorksheetFunction.SumIf. The problem
is, the formula does not get placed in the cell. The calculated value gets
placed in the cell instead. Is there any way to keep the formula?

Thanks in advance
Ben
 
F

Frank Kabel

Hi
just a guess: you probably have a line in your code like
Range("A1").value = Application.WorksheetFunction.SumIf(..)
or
Range("A1") = Application.WorksheetFunction.SumIf(..)

This only fills the VALUE of the cell.

If you want to insert a formula you have to use the range's formula
property
RANGE("A1").formula = "=SUMIF(...)"
or in R1C1 notation
RANGE("A1").formulaR1C1 = "=SUMIF(...)"
 
B

Ben

Thanks Frank.

I was hoping to use the WorksheetFunction.SumIf function as it takes the
ranges in a nice way. I have put the .Formula on the cell for setting but
it still just puts the calcuulated value in.

This is what I have...

Set textRange = gWorkBook.Names(reportGroupColumn).RefersToRange
Set dataRange = gWorkBook.Names("Date1").RefersToRange
excelRange.Formula =
gExcelObj.Application.WorksheetFunction.SumIf(textRange, strNextReportGroup,
dataRange)

If that is not possible, is there an easy way to get the ranges into a text
format so I can put them in the "=SUMIF(.....)" statement?
 
F

Frank Kabel

Hi
you still assign only a value to that range. The formula property
expects a formula as it would have been written directly in the
worksheet. So try something like
excelRange.Formula = "SUMIF(defined_name1,""<1"",defined_name2)"
 
B

Ben

Oh, OK. Thanks a lot.

Frank Kabel said:
Hi
you still assign only a value to that range. The formula property
expects a formula as it would have been written directly in the
worksheet. So try something like
excelRange.Formula = "SUMIF(defined_name1,""<1"",defined_name2)"
Click to expand...
 
A

A.W.J. Ales

Small correction on the formula Frank gave
Make it : excelRange.Formula = "=SUMIF(defined_name1,""<1"",defined_name2)"
as he mentioned in his previous posting.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
B

Ben

Hi again

I am trying to sum on multiple conditions, but SUMIF does not seem to allow
for this.
I have tried the following, but it only seems to perform the condition on a
single cell rather than throughout the whole named range.

=SUM(IF( (defined_name1="DoSumming")*(defined_name2="DoSumming"),
defined_name2))

Thanks...
 
F

Frank Kabel

Hi
try
=SUMPRODUCT((defined_name1="DoSumming")*(defined_name2="DoSumming"),def
ined_name2)
 
B

Ben

Oh thanks. I worked out the problem with mine. You need to press
Ctrl+Shift+Enter after typing in your formula in excel to enter it as an
array formula. To achieve the same effect in VBA, you need to use the
range.FormulaArray property instead of the range.Formula property.
 
Top