Is there a good way to deploy formulas when embedding formulas in cells with VBA?

Instead of adding cells in the VBA,
When I wanted to embed an expression, I came up with a way to press it with a string.
I succeeded just in case.I thought this was too redundant.

I asked a similar question before, but is there any way to expand the expression like ruby in VBA?

subcells_sum
  for i=1 to sheets.count
    for j = 4 to 34
      sheets(i).cells(j,5)="=sum(b"+cstr(j)+":c"+cstr(j)+")" 
  next j —next i
end sub

It seems that format() can be realized, but it's hard to get to the information.
　　

subfo
dims as string
dim array(100) as integer
for i = 0 to 100
  array(i) = i+1
next i
for each a in array
  a = cstr(a)
  range("a1") = format(a, "=sum(a@:b@)")
next a
end sub'NG

vba

2022-09-30 18:11

3 Answers

Do you mean you want to embed formula =SUM(B4:C4) in cell E4? This formula is a widely used A1 format expression in Excel, but there is another R1C1 format, which makes it easy to express =SUM(RC[-3]:RC[-2]) and relative addresses.As a result, the formula becomes a fixed string.

subcells_sum
  for i=1 to sheets.count
    for j = 4 to 34
      sheets(i).cells(j,5).FormulaR1C1="=SUM(RC[-3]:RC[-2])" 
  next j —next i
end sub

will do.Furthermore, you can replace it all at once by specifying a range.

subcells_sum
  for i=1 to sheets.count
    sheets(i).range("E4:E34").FormulaR1C1="=SUM(RC[-3]:RC[-2])" 
  next i
end sub

When dealing with more complex formulas, you can use the ADDRESS standard function to generate a string representing a cell.This string gives you the flexibility to build formulas.

2022-09-30 18:11

Other measures (paste the copy)

subcopy_calc
  range("c1").copy
  for i = 2 to 5
  sheets(1).paste destination: =cells(i,3)
  next i
end sub

formula

It seems that you can enter formulas in bulk with the syntax formula.I haven't tried this one.

2022-09-30 18:11

It's not a standard feature of VBA.

If you use Ariawase, a library published by a person named Igeta, you can do something like that.Library Location
https://github.com/vbaidiot/Ariawase

Description
http://igeta-diary.blogspot.com/2014/03/

Use the Formats function in Core.bas.

Sheets(i).Cells(j,5).Formula=Formats("=SUM(B{0}:C{0})",j)

I can write like this.

2022-09-30 18:11

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656