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

问题描述

  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 
 
 
范围（C2：G& Lastro）。选择
 
带范围（C2：G& Lastro）
 .Range（C2：G& Lastro）.Formula == IF（oRo * nRo <0，ROUNDUP（oRo * 1.3，-2），IF（ABS（oRo）<= ABS（nRo） ，ROUNDUP（oRo * 1.3，-2）））
结束
 
结束Sub 
  

解决方案

最好的可维护性是在从VBA中将公式写入Excel时使用R1C1表示法。我仍然不能读取R1C1，但是我专门用它来编写VBA中的公式。以下是如何：

假设您想在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中，键入
 ？activecell.FormulaR1C1 
  
这将给你所有你需要的。您无需读取它，只需要确保在单元格G2中键入正确的公式（以A1符号表示）。现在你可以有超简单的代码，如
  Dim lRow As Long 
 Dim sh As Worksheet 
 
设置sh = ActiveSheet 
 
 lRow = sh.Cells（sh.Rows.Count，2）.End（xlUp）.Row  -  1 
 sh.Range（G2）。调整大小（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公式复制到代码中。
 
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

解决方案
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:

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)))
So type that in G2, select G2, and open the Immediate Window (Ctrl+G in VBE). In the IW, type
?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
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.

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