Excel VBA-将公式直接插入具有变量引用的一组行中，或替换一个字符串，例如"\ ="与"=" [英] Excel VBA - insert formula in a set of rows with variable reference directly or replacing a string for example "\=" with "="

问题描述

我的目标是在一组行中编写一个公式.公式中的某些引用必须更改每一行.

I have the goal to write a formula in a set of rows. Some references in the formula have to change each row.

我实现了以下脚本:

Dim i As Integer
Dim formcolM As String
Dim temprng As String

For i = 0 To 100  
    formcolM = "NUMBERVALUE(IF(Q" & i & "=""Bedarf kum."";A" & i & ";IF(Q" & i & "=""Ist"";OFFSET(A" & i & ";-1;0);IF(Q" & i & "=""Lz."";OFFSET(A" & i & ";-2;0);IF(Q" & i & "=""Ist+Lz.-Bedarf"";OFFSET(A" & i & ";-3;0);)))))"
Let temprng = "M" & i
Range(temprng).Select
ActiveCell.Value = "\=" & formcolM
next i

使用此脚本，我在excel表的M列的每一行写了一个字符串.

With this script I am writing a string each row in my excel table at column M.

我注意到，如果公式中没有符号"\"，则会发现错误.

I noticed that if the formula hasn't the symbol "\" , you can find an error .

为了避免该错误，我想到了保留符号"\"并使用技巧在之后将其删除(因为我不知道如何用R1C1公式求解.我在Stackoverflow上阅读了一些答案，但不幸的是，我不明白)

In order to avoid the error I thought to leave the symbol "\" and to use a trick deleting it after (because I don't know how to solve with R1C1 formula. I read some answers on Stackoverflow, but unfortunately I did not understand )

for循环后的替换脚本:

The replacing script after the for cycle:

Columns("M:M").Replace What:="\=", Replacement:="=", LookAt:=xlPart

奇怪的是，宏没有将其删除.实际上，脚本完成后，似乎什么都没有发生，没有错误.但是，如果我想用另一个符号(例如"*")替换"\ =，那么替换脚本就可以了.

The strange thing is that the macro doesn't delete it. Infact when the script finishes , it seems that nothing happened, without errors. But if I want substitute "\=" with another symbol, for example "*", the replacing script works.

我不明白问题是否出在:

I did not understand if the problem is :

• replace方法无法识别符号"="以进行搜索
• 我不能使用replace方法，因为符号"="会以某种方式干扰，我不知道会发生什么.

OR ，还有另一种最简单的方法来完成此任务吗?

OR, is there another simplest way to get this task done?

有人可以帮助我进行修复吗?我应该使用vba自动在M列中使用公式(而不是使用Excel工作表中的另一个公式).

Someone could help me in order to fix? I should have the formula working in the column M , automatically with vba (not with another formula in the excel sheet) .

提前感谢您的时间.

推荐答案

我们可以直接应用公式.问题在于，vba是非常以US-EN为中心的，使用 .Formula 时的所有公式都必须采用该格式.

We can apply the formula directly. The issue is that vba is very US-EN Centric and all formula when using the .Formula needs to be in that format.

此外，由于您的公式引用的行上方是第3行中的值，因此我们需要在 4 而不是 0 处开始循环.没有行 0

Also since your formula refers to values in a row 3 above the one in which it is put we need to start the loop at 4 not 0. There is no row 0

有两种方法，以US-En格式(具有英语功能和)作为使用 .Formula 的分隔符:

There are two ways, in US-En format with English functions and , as the deliminator using .Formula:

Dim i As Integer

For i = 4 To 100
    Range("M" & i).Formula = "=NUMBERVALUE(IF(Q" & i & "=""Bedarf kum."",A" & i & ",IF(Q" & i & "=""Ist"",OFFSET(A" & i & ",-1,0),IF(Q" & i & "=""Lz."",OFFSET(A" & i & ",-2,0),IF(Q" & i & "=""Ist+Lz.-Bedarf"",OFFSET(A" & i & ",-3,0),)))))"
Next i

或使用 .FormulaLocal 和公式(以您的母语编写).

Or using .FormulaLocal and the formula as you would write it in your native tongue.

Dim i As Integer

For i = 4 To 100
    Range("M" & i).FormulaLocal = "=NUMERO.VALORE(SE(Q" & i & "=""Bedarf kum."";A" & i & ";SE(Q" & i & "=""Ist"";SCARTO(A" & i & ";-1;0);SE(Q" & i & "=""Lz."";SCARTO(A" & i & ";-2;0);SE(Q" & i & "=""Ist+Lz.-Bedarf"";SCARTO(A" & i & ";-3;0);)))))"
Next i

这篇关于Excel VBA-将公式直接插入具有变量引用的一组行中，或替换一个字符串，例如"\ ="与"="的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！