1

This except of VBA code

summ = "СУММ(AQ" + Format(first) + ":AX" + Format(last - 1) + ")"
cell = "AQ" + Format(last) + ":AX" + Format(last)
r.range(cell).Formula = "=" + summ

should insert a formula, e.g. =СУММ(DW6:EI18) into DW19.

(СУММ is a Russian localized name for SUM)

What happens is that correct formula above appears at its proper place but displays a #NAME error. If I select the cell, put the cursor on the formula, and press enter, the formula doesn't change but starts working.

How to make the code above work?

Community
  • 1
  • 1
ilya n.
  • 18,398
  • 15
  • 71
  • 89
  • It's not related to your problem, but you should get out of the habit of using "+" for string concatenation. Use "&" instead. – Gary McGill Nov 12 '09 at 17:18
  • Sorry for double-posting. The corporate firewall was blocking SO and I didn't know any of my questions was posted. Adminitrators unblocked SO today. – ilya n. Nov 13 '09 at 07:27

4 Answers4

6

You need to set FormulaLocal instead of Formula because you use the Russian functionnames.

r.range(cell).FormulaLocal = "=" + summ

marg
  • 2,817
  • 1
  • 31
  • 34
3

I had the same problem, with other function however.

I fix the problem giving the name of all functions in English. I mean, the function O --> Or, the function SI --> IF and so on.

I work in the Spanish version.

I hope my answer be useful.

Jonathan Largo
  • 31
  • 1
0

I think it is the FormulaLocal answer from Marg (1st answer) even though OP hasn't marked it. There is nothing wrong with the formula itself.

Anonymous Type
  • 3,051
  • 2
  • 27
  • 45
-1

Try this instead

summ = "=СУММ(AQ" + Format(first) + ":AX" + Format(last - 1) + ")"
cell = "AQ" + Format(last) + ":AX" + Format(last)
r.range(cell).Formula = summ
guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • Nope. Absolutely the same value will be passed to the Formula property in either case. – Gary McGill Nov 12 '09 at 17:19
  • I was thinking it may have had something to do with the + in the last line. Maybe VBA was converting erroneously the type instead of merely appending. – guitarthrower Nov 12 '09 at 18:12