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.

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.

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

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.

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.