将 IF 条件应用于范围 Excel VBA [英] Apply IF condition to a range Excel VBA

问题描述

我已经写了这段代码，但它似乎不起作用，为什么?

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

推荐答案

在将公式从 VBA 写入 Excel 时，最好的可维护性选择是采用 R1C1 表示法.我仍然看不懂 R1C1，但我专门用它来从 VBA 编写公式.方法如下:

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:

假设你想在 G2 中使用这个公式

Let's say you want this formula in G2

=IF(J2*Q2<0,ROUNDUP(J2*1.3,-2),IF(ABS(J2)<=ABS(Q2),Q2,ROUNDUP(J2*1.3,-2)))

所以在 G2 中输入，选择 G2，然后打开即时窗口(在 VBE 中为 Ctrl+G).在 IW 中，键入

So type that in G2, select G2, and open the Immediate Window (Ctrl+G in VBE). In the IW, type

?activecell.FormulaR1C1

这将为您提供所需的一切.您不必能够阅读它，您只需确保在单元格 G2 中输入了正确的公式(以 A1 表示法).现在你可以拥有超级简单的代码，比如

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

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)))"

我所做的只是从即时窗口复制 R1C1 公式并将其粘贴到代码中.

All I did was copy the R1C1 formula from the Immediate Window and paste it into the code.

这篇关于将 IF 条件应用于范围 Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！