将 IF 条件应用于范围 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19501846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apply IF condition to a range Excel VBA
提问by Shan S
I have written this code but it doesnt seems to be working , Why?
我写了这段代码，但它似乎不起作用，为什么？
Dim oRo As String
Dim nRo As String
Lastro = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
oRo = "J2:O" & Lastro
nRo = "Q2:V" & Lastro
Range("C2:G" & Lastro).Select
With Range("C2:G" & Lastro)
.Range("C2:G" & Lastro).Formula = "=IF(oRo*nRo<0,ROUNDUP(oRo*1.3,-2),IF(ABS(oRo) <=ABS(nRo),nRo,ROUNDUP(oRo*1.3,-2)))"
End With
End Sub
回答by Dick Kusleika
Your best bet for maintainability is to embrace R1C1 notation when you write formulas to Excel from VBA. I still can't read R1C1, but I use it exclusively to write formulas from VBA. Here's how:
可维护性的最佳选择是在从 VBA 将公式写入 Excel 时采用 R1C1 表示法。我仍然无法读取 R1C1，但我专门用它来编写 VBA 中的公式。就是这样：
Let's say you want this formula in G2
假设你想在 G2 中使用这个公式
=IF(J2*Q2<0,ROUNDUP(J2*1.3,-2),IF(ABS(J2)<=ABS(Q2),Q2,ROUNDUP(J2*1.3,-2)))
So type that in G2, select G2, and open the Immediate Window (Ctrl+G in VBE). In the IW, type
所以在 G2 中输入它，选择 G2，然后打开立即窗口（VBE 中的 Ctrl+G）。在 IW 中，键入
?activecell.FormulaR1C1
That will give you all that you need. You don't have to be able to read it, you just have to be sure you typed the right formula (in A1 notation) in cell G2. Now you can have super simple code like
这会给你你需要的一切。您不必能够阅读它，只需确保在单元格 G2 中键入正确的公式（以 A1 表示法）。现在你可以拥有超级简单的代码，比如
Dim lRow As Long
Dim sh As Worksheet
Set sh = ActiveSheet
lRow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Row - 1
sh.Range("G2").Resize(lRow, 1).FormulaR1C1 = "=IF(RC[3]*RC[10]<0,ROUNDUP(RC[3]*1.3,-2),IF(ABS(RC[3])<=ABS(RC[10]),RC[10],ROUNDUP(RC[3]*1.3,-2)))"
All I did was copy the R1C1 formula from the Immediate Window and paste it into the code.
我所做的只是从立即窗口复制 R1C1 公式并将其粘贴到代码中。
回答by pinkfloydx33
.Range("C2:G" & Lastro).Formula = "=IF(" & oRo & "*" & nRo & "<0,ROUNDUP(" & oRo & "*1.3,-2),IF(ABS(" & oRo & ") <=ABS(" & nRo & ")," & nRo & ",ROUNDUP(" & oRo & "*1.3,-2)))"
You are hardcoding the phrases "oRo" and "nRo"
您正在对短语“oRo”和“nRo”进行硬编码
回答by SeanC
When you get to this line to write the formula, you are not writing out the values of oRo and nRo, so excel is expectin them to be defined within name manager.
当您到达这一行来编写公式时，您并没有写出 oRo 和 nRo 的值，因此 excel 期望它们在名称管理器中定义。
.Range("C2:G" & Lastro).Formula = "=IF(oRo*nRo<0,ROUNDUP(oRo*1.3,-2),IF(ABS(oRo) <=ABS(nRo),nRo,ROUNDUP(oRo*1.3,-2)))"
If you want to write out the calculated values of oRo and nRo, you will have to alter the code slightly:
如果要写出 oRo 和 nRo 的计算值，则必须稍微更改代码：
Dim oRo As String
Dim nRo As String
Lastro = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
oRo = "J2:O" & Lastro
nRo = "Q2:V" & Lastro
Range("C2:G" & Lastro).Formula = _
"=IF(" & oRo & "*" & nRo & "<0,ROUNDUP(" & oRo & _
"*1.3,-2),IF(ABS(" & oRo & ") <=ABS(" & nRo & _
")," & nRo & ",ROUNDUP(" & oRo & "*1.3,-2)))"
End Sub
notice no selectis required, and as you are only dealing with one range, no withis required either. I would also suggest you add in Option Explicitto the top of your procedure to ensure all variables are declared and spelled correctly.
注意 noselect是必需的，因为您只处理一个范围，所以也不with需要。我还建议您添加Option Explicit到程序的顶部，以确保所有变量都被正确声明和拼写。
