SUMIF not working when used in VBA
 

When I use the workbooks.range.formular1c1 object in VBA, the formula's do
not calculate and instead show #NAME? as the value. I can edit each cell and
then press ENTER to get the values to update, but is there a way to get the
values to show as soon as the code completes?

The VBA statement is

worksheet.Range("B2").FormulaR1C1 = "=sumif(sheet1!A:A,rc[-1],sheet1!D:D)"

to sum all the rows in column D of sheet1 where the column A row = the value
in cell A2 on the current sheet. There is no problem with the formula
itself, since just selecting the cell and pressing F2 and ENTER updates the
value.

SUMIF not working when used in VBA
 

maybe turn calculation to automatic and make sure the cell isn't formatted as
Text.

--
Regards,
Tom Ogilvy


"spud" wrote:

When I use the workbooks.range.formular1c1 object in VBA, the formula's do
not calculate and instead show #NAME? as the value. I can edit each cell and
then press ENTER to get the values to update, but is there a way to get the
values to show as soon as the code completes?

The VBA statement is

worksheet.Range("B2").FormulaR1C1 = "=sumif(sheet1!A:A,rc[-1],sheet1!D:D)"

to sum all the rows in column D of sheet1 where the column A row = the value
in cell A2 on the current sheet. There is no problem with the formula
itself, since just selecting the cell and pressing F2 and ENTER updates the
value.

SUMIF not working when used in VBA
 

Tom - thanks. I used calculation both ways, and formatted the cells as 0.00
number format in the macro but the problem persists.

SUMIF not working when used in VBA
 

You are mixing reference types (A1 vs R1C1) in your formula.
Use A1 type only in the "Formula" property.
Use R1C1 type only in the "FormulaR1C1" property.
--
Festina Lente


"spud" wrote:

When I use the workbooks.range.formular1c1 object in VBA, the formula's do
not calculate and instead show #NAME? as the value. I can edit each cell and
then press ENTER to get the values to update, but is there a way to get the
values to show as soon as the code completes?

The VBA statement is

worksheet.Range("B2").FormulaR1C1 = "=sumif(sheet1!A:A,rc[-1],sheet1!D:D)"

to sum all the rows in column D of sheet1 where the column A row = the value
in cell A2 on the current sheet. There is no problem with the formula
itself, since just selecting the cell and pressing F2 and ENTER updates the
value.

SUMIF not working when used in VBA
 

As PAPADOS discerningly observed (and I overlooked), you are mixing your form
of addressing.

worksheet.Range("B2").FormulaR1C1 = "=sumif(sheet1!A:A,rc[-1],sheet1!D:D)"

should be

worksheet.Range("B2").FormulaR1C1 = "=sumif(sheet1!C1,rc[-1],sheet1!C4)"

--
Regards,
Tom Ogilvy



"spud" wrote:

Tom - thanks. I used calculation both ways, and formatted the cells as 0.00
number format in the macro but the problem persists.