WorksheetFunction

  • Thread starter Thread starter Ben
  • Start date Start date
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
 
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(...)"
 
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?
 
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)"
 
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...
 
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 *
 
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...
 
Hi
try
=SUMPRODUCT((defined_name1="DoSumming")*(defined_name2="DoSumming"),def
ined_name2)
 
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.
 
Back
Top