代码之家 › 专栏 › 技术社区 › RugsKid

用VBA将复杂嵌套IF和公式写入工作表

excel-formula vba excel

1

RugsKid · 技术社区 · 7 年前

我目前有一个复杂的公式,我正试图通过VBA放入一个细胞。当前工作公式(在单元格BP2中)如下:

'=IF(X2="No Bonus",1,
'IF(AND(((AG2-AF2+1)/(365/12))>=50,AD2="N"),5,
'IF(AND(((AG2-AF2+1)/(365/12))>=40,AD2="N"),4,
'IF(AND(((AG2-AF2+1)/(365/12))>=30,AD2="N"),2,
'IF(AND(((AG2-AF2+1)/(365/12))>=20,AD2="N"),1,
'IF(AND(((AG2-AF2+1)/(365/12))>0,AD2="N"),0.5,
'IF(AND(((AG2-AF2+1)/(365/12))>=20,AD2="Y"),1,
'IF(AND(((AG2-AF2+1)/(365/12))>=15,AD2="Y"),2,
'IF(AND(((AG2-AF2+1)/(365/12))>=0,AD2="Y"),0.3,0)))))))))

我将代码放在BP列中现有数据下方的可变范围内。我的代码如下:

Dim LastRowExisting As Long
Dim LastRowNew As Long

LastRowExisting = Worksheets("MyWorksheet").Range("CE1").Value 'LastRow of Existing data stored here by macro earlier
LastRowNew = Worksheets("MyWorksheet").Range("A" & rows.Count).End(xlUp).Row 'requires Column A "Source" is always populated on all lines    

Worksheets("MyWorksheet").Range("BP" & LastRowExisting + 1 & ":" & "BP" & LastRowNew).FormulaR1C1 = "=IF(RC[-44]=""No Bonus"",""1"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=50,RC[-38]=""N"",""5"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=40,RC[-38]=""N"",""4"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=30,RC[-38]=""N"",""2"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=20,RC[-18]=""N"",""1"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=0,RC[-38]=""N"",""0.5"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=20,RC[-38]=""Y"",""1"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=15,RC[-38]=""Y"",""2"", _
IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=0,RC[-38]=""Y"",""0.3"",""0"")))))))))"

我在公式行上得到一个运行时错误“1004”“应用程序定义的或对象定义的错误”。我试着把这个放在一条长线上,看看我的断线是否是问题所在,但我仍然得到同样的错误。

我意识到这可能是一个语法问题,但我很难看到什么是关闭。我知道这是一个不太理想(即丑陋)的解决方案,但鉴于这发生在一个更大的宏,我希望使这个公式方法的工作。

(AG-AF+1)/(365/12) 在一定范围内,第二个是简单的 Y N 标志进入 Column AD (AG2-AF2+1)/(365/12) = 45 和 AD2 = N 那我想 BP2 = 4

干杯!

3 回复 | 直到 7 年前

1

3

QHarr 7 年前

作为选择案例写作可能是一个好主意,但记住宏记录器可以在这里你的朋友。只需在单元格BP2中记录输入公式,并将输出复制到ActiveCell.FormulaR1C1之后:

 ActiveCell.FormulaR1C1 = _
        "=IF(RC[-44]=""No Bonus"",1,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=50,RC[-38]=""N""),5,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=40,RC[-38]=""N""),4,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=30,RC[-38]=""N""),2,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=20,RC[-38]=""N""),1,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>0,RC[-38]=""N""),0.5,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=2" & _
        "0,RC[-38]=""Y""),1,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=15,RC[-38]=""Y""),2,IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=0,RC[-38]=""Y""),0.3,0)))))))))" & _
        ""

2

1

scenography 7 年前

请这样尝试代码,其中每一行都是引号中的单独字符串。

Worksheets("MyWorksheet").Range("BP" & LastRowExisting + 1 & ":" & "BP" & _
    LastRowNew).FormulaR1C1 = "=IF(RC[-44]=""No Bonus"",""1"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=50,RC[-38]=""N"",""5"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=40,RC[-38]=""N"",""4"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=30,RC[-38]=""N"",""2"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=20,RC[-18]=""N"",""1"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=0,RC[-38]=""N"",""0.5"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=20,RC[-38]=""Y"",""1"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=15,RC[-38]=""Y"",""2"", " & _
    "IF(AND(((RC[-35]-RC[-36]+1)/(365/12))>=0,RC[-38]=""Y"",""0.3"",""0"")))))))))"

行连续字符(下划线)可以在字符串之间使用,如处的示例所示 How to Break and Combine Statements in Code

3

0

VBasic2008 7 年前

键入(&A);键入(&A);打字

打开工作簿,打开VBE,插入新模块(或使用现有模块),然后将以下代码粘贴到模块中:

Option Explicit

Function Nested(Range1 As Range, Range2 As Range, _
    Range3 As Range, Range4 As Range) As Single

'**** Customize BEGIN ******************
  'NOCase
  Const cStrSearch As String = "No Bonus"
  Const cSglYes As Single = 1
  'Period
  Const cDays As Integer = 365
  Const cMonths As Integer = 12
  'YesNo
  Const cStrYes As String = "Y"
  Const cStrNo As String = "N"
  'Title1 Source
  Const cSglN1 As Single = 50
  Const cSglN2 As Single = 40
  Const cSglN3 As Single = 30
  Const cSglN4 As Single = 20
  Const cSglN5 As Single = 0
  'Title1 Target
  Const cSglNA1 As Single = 5
  Const cSglNA2 As Single = 4
  Const cSglNA3 As Single = 2
  Const cSglNA4 As Single = 1
  Const cSglNA5 As Single = 0.5
  'Title2 Source
  Const cSglY1 As Single = 20
  Const cSglY2 As Single = 15
  Const cSglY3 As Single = 0
  'Title2 Target
  Const cSglYA1 As Single = 1
  Const cSglYA2 As Single = 2
  Const cSglYA3 As Single = 0.3
'**** Customize END ********************

  If Range1.Value2 = cStrSearch Then
    Nested = cSglYes
    Exit Function
  End If

  Nested = (Range4.Value2 - Range3.Value2) / cDays / cMonths

  Select Case UCase(Range2.Text) 'Remove 'Ucase' function for case-sensitive
    Case cStrNo
      Select Case Nested
        Case Is >= cSglN1: Nested = cSglNA1
        Case Is >= cSglN2: Nested = cSglNA2
        Case Is >= cSglN3: Nested = cSglNA3
        Case Is >= cSglN4: Nested = cSglNA4
        Case Is > cSglN5: Nested = cSglNA5
        Case Else
          Nested = 0 'Change if necessary
      End Select
    Case cStrYes
      Select Case Nested
        Case Is >= cSglY1: Nested = cSglYA1
        Case Is >= cSglY2: Nested = cSglYA2
        Case Is >= cSglY3: Nested = cSglYA3
        Case Else
          Nested = 0 'Change if necessary
      End Select
    Case Else
      Nested = 0 'Change if necessary
  End Select

'=IF(X2="No Bonus",1,
'IF(AND(((AG2-AF2+1)/(365/12))>=50,AD2="N"),5,
'IF(AND(((AG2-AF2+1)/(365/12))>=40,AD2="N"),4,
'IF(AND(((AG2-AF2+1)/(365/12))>=30,AD2="N"),2,
'IF(AND(((AG2-AF2+1)/(365/12))>=20,AD2="N"),1,
'IF(AND(((AG2-AF2+1)/(365/12))>0,AD2="N"),0.5,
'IF(AND(((AG2-AF2+1)/(365/12))>=20,AD2="Y"),1,
'IF(AND(((AG2-AF2+1)/(365/12))>=15,AD2="Y"),2,
'IF(AND(((AG2-AF2+1)/(365/12))>=0,AD2="Y"),0.3,0)))))))))
End Function

关闭VBE并在中的工作表(Excel)中第二行单元格(除 X2 , AD2 , AF2 AG2 )(例如。 BP2 如您所述)使用以下公式:

=NESTED($X2,$AD2,$AF2,$AG2)

将公式复制/粘贴到需要的地方。