将具有串联部分的公式写入单元格 [英] Writing a formula with concatenated parts into a cell

问题描述

方案::我有一个代码，该代码应将公式写入工作表单元格.此公式用于API检索某些值.我的公式位于循环中(此操作针对多列完成)，并引用第一行作为标识符.

Scenario: I have a code that should write a formula to a worksheet cells. This formula is for an API to retrieve some value. My formula is inside a loop (this is done for multiple columns) and references the first row for an identifier.

原始公式:

=FS(B1;"FI(DATE,,DATE)")

带有浮动引用(在循环内)的修改后公式:

For i = 1 To lColumn
    If wb.Worksheets("Dates").Cells(i, 1).Value <> "" Then
        wb.Worksheets("Dates").Cells(i,2).value = "=FS(" & i & "1;"FI(DATE,,DATE)")"
    End If
Next i

lColumn是一些预定义的数字.

Where lColumn is some pre-defined number.

问题:我在循环的公式部分不断收到意外的语句结尾"错误.

Issue: I keep getting the "Unexpected end of statement" error in the formula part of the loop.

我已经尝试过的方法:我尝试了不同的变体，例如，重新定位了"s"和"s".

What I already tried: I tried different variations, repositioning the "s and 's, for example:

wb.Worksheets("Dates").Cells(i,2).value = "'"=FS(" & i & "1;"FI(DATE,,DATE)")""

或

wb.Worksheets("Dates").Cells(i,2).value = "'=FS(" & i & "1;"FI(DATE,,DATE)")"

或

wb.Worksheets("Dates").Cells(i,2).value = "'""=FS(" & i & "1;"FI(DATE,,DATE)")"

，依此类推.但是错误仍然存​​在.

and so on. But the error still persists.

问题:执行此操作的正确方法是什么?

Question: What is the proper way to do this operation?

推荐答案

在VBA中使用公式有点棘手:

Working with formulas in VBA is a little bit tricky:

• 要编写公式，请使用 range.formula 属性，而不要使用 .value .

您必须像使用英文Excel一样编写公式.参数分隔符是逗号(不是分号).

You have to write the formula as if you are using an english Excel. Parameter-separator is comma (not semicolon).

尝试(未经验证，因为您需要的公式对我们无效):

Try (untested as the formula you need is not valid to us):

with wb.Worksheets("Dates")
  dim f as string, adr as string
  adr = cells(i, 1).address(false, false)   ' get rid of Dollar signs
  f = "=FS(" & adr & ",""FI(DATE,,DATE)"")"
  .Cells(i, 2).formula = f
end with

这篇关于将具有串联部分的公式写入单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！