在excel公式中使用变量 [英] use variable in excel formula

问题描述

这是我的代码示例，但我被困住，因为我不会不知道在单元格公式中包含变量的语法。

 表格（Orderboek）。选择
 
范围（H&（rOi + 1））。FormulaR1C1 == Sum（H3：H& rOi&）
  

我的变量声明如下：

  Dim i As Integer ，a As Range'i = index a een gebied 
 Dim prText As String'product text 
 Dim rOi As Long'rij nummer in orderboek 
 Dim rng，sumrng As Excel.Range 
 Dim r As long'rij number in offerte 
 Dim Tot As Long'totaal som van offerte 
 Dim ws As Excel.Worksheet 
  

非常感谢，如果你能帮助我。

解决方案

  Range（H&（rOi + 1））。Formula == Sum（H3：H&am p; rOi&）
  

确保使用 .Formula 而不是 .FormulaR1C1 在这种情况下。还要确保&符号之间有空格。

I'm trying to make an automated sum based on an increasing number of rows.

Here is my sample of code but I'm stuck as I don't know the syntax to include a variable in the formula of a cell.

Sheets("Orderboek").Select

Range("H" & (rOi + 1)).FormulaR1C1 = "=Sum(H3:H"&rOi&")"

My variables are declared as follows:

Dim i As Integer, a As Range    'i= index a een gebied
Dim prText As String    'product text
Dim rOi As Long         'rij nummer in orderboek
Dim rng, sumrng As Excel.Range
Dim r As Long           'rij number in offerte
Dim Tot As Long         'totaal som van offerte
Dim ws As Excel.Worksheet

Thanks very much if you can help me out.

解决方案

Try this:

Range("H" & (rOi + 1)).Formula = "=Sum(H3:H" & rOi & ")"

Make sure you use .Formula and not .FormulaR1C1 in this case. Also make sure that there are spaces between ampersands.

这篇关于在excel公式中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！