[已解决]在单元格生成公式

yeu118

=ROUND((I6+J6+K6+L6+N6-O6-P6)*1,0)
我要在VBA里面写，我写成这样
Cells(j, 17) = Application.WorksheetFunction.Round(("I" & j + "j" & j + "k" & j + "l" & j + "n" & j - "o" & j - "p" & j) * 1, 0)
但是，提示类型不匹配





我要的是在单元格里生成的是公式=ROUND((I6+J6+K6+L6+N6-O6-P6)*1,0),不要直接得到数字

最佳答案

月排行榜 / 总排行榜

suye1010

2012-6-13 19:10

Cells(j, 17).Formula="=Round((I" & j & " + j" & j &"+ k" & j &"+ l" & j &"+ n" & j &" - o" & j &"- p" & j&") * 1, 0)"

可以用变量来生成红色string部分，然后引用即可

跳转到最佳答案楼层

suye1010

Cells(j, 17).Formula="=Round((I" & j & " + j" & j &"+ k" & j &"+ l" & j &"+ n" & j &" - o" & j &"- p" & j&") * 1, 0)"

可以用变量来生成红色string部分，然后引用即可

yeu118

suye1010 发表于 2012-6-13 19:10
Cells(j, 17).FormulaR1C1 = _
        "=ROUND((R[3]C[4]+R[3]C[5]+R[3]C[6]+R[3]C[7]+R[3]C[9]-R[3]C[10 ...

我这个J代表的是行数，你这个公式，我怎么看不太懂呢

suye1010

这个是VBA帮助中的内容
Returns or sets a Variant value that represents the object's formula in A1-style notation and in the language of the macro.
Syntax

expression.Formula

expression   A variable that represents a Range object.

Remarks


This property is not available for OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) data sources.

If the cell contains a constant, this property returns the constant. If the cell is empty, this property returns an empty string. If the cell contains a formula, the Formula property returns the formula as a string in the same format that would be displayed in the formula bar (including the equal sign).

If you set the value or formula of a cell to a date, Microsoft Excel checks to see whether that cell is already formatted with one of the date or time number formats. If not, Microsoft Excel changes the number format to the default short date number format.

If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array.

Setting the formula for a multiple-cell range fills all cells in the range with the formula.


Example


This example sets the formula for cell A1 on Sheet1.

Visual Basic for Applications
Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"




The following code example was provided by MVP Bill Jelen. Bill is the author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.


The following code example sets the formula for cell A1 on Sheet1 to display today's date.
Visual Basic for Applications
Sub InsertTodaysDate()
    ' This macro will put today's date in cell A1 on Sheet1
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.Formula = "=text(now(),""mmm dd yyyy"")"
    Selection.Columns.AutoFit
End Sub

yeu118

suye1010 发表于 2012-6-13 19:10
Cells(j, 17).Formula="=Round(("I" & j + "j" & j + "k" & j + "l" & j + "n" & j - "o" & j - "p" & j) * ...

你这样，显示语法是错误的啊。

suye1010

yeu118 发表于 2012-6-13 19:27
你这样，显示语法是错误的啊。

已更新。请查看原帖。

yeu118

suye1010 发表于 2012-6-13 19:50
已更新。请查看原帖。

好的——谢谢……。

陈明浩

dgdfgdfgdfgdfgdfgdfgdfgdfgdfgdfgdfgdfgdfg

陈明浩

sgdedeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee