时间戳

首页 > 解决方案 > 将 Excel 数组公式转换为 VBA 代码

excel - 将 Excel 数组公式转换为 VBA 代码

问题描述

大家好
，我在 excel 中有这个数组公式，我想将它合并到 VBA 代码中，但它给了我一个错误：无法设置FormulaArrayRange 类的属性（错误 1004）
这是我修改后的公式编码

"=IF(SUMPRODUCT(--(($D$2=$B9)*(E$7>=$D$3)*(E$7<=$D$4)*(NOT(E$6>=1))*(NOT(E$8='الجمعة'))))=1;VLOOKUP($D$5;LeavesTypes;2;0);"""")"

这是完整的代码

Option Explicit
Option Base 1
Dim wrk As Workbook, DataSH As Worksheet, CurrentSH As Worksheet
Dim FirstCol As Integer, EmpRow As Integer, EmpID As String
Dim GetEmpRowLoop As Integer, StartingEmpCol As Integer
Dim StartColLoop As Integer, EndingEmpCol As Integer
Dim EndColLoop As Integer
Public Sub Macro1()

    Set wrk = ThisWorkbook
    Set DataSH = wrk.Worksheets("الاجازات ")
    Set CurrentSH = wrk.ActiveSheet

    If CurrentSH.Name = DataSH.Name Then
        MsgBox "Please Navigate to an Active Sheet", vbCritical
        Exit Sub
    End If
    
    EmpID = CurrentSH.Range("d2").Value
    
    For GetEmpRowLoop = 9 To CurrentSH.Cells(Cells.Rows.Count, 2).End(xlUp).Row
        If CurrentSH.Cells(GetEmpRowLoop, 2).Value = EmpID Then
            EmpRow = GetEmpRowLoop
            Exit For
        End If
    Next GetEmpRowLoop
    
    For StartColLoop = 4 To CurrentSH.Cells(EmpRow, Cells.Columns.Count).End(xlToRight).Column
        If CurrentSH.Cells(7, StartColLoop).Value = CurrentSH.Range("D3").Value Then
            StartingEmpCol = StartColLoop
            Exit For
        End If
    Next StartColLoop
    
    For EndColLoop = 4 To CurrentSH.Cells(EmpRow, Cells.Columns.Count).End(xlToRight).Column
        If CurrentSH.Cells(7, EndColLoop).Value = CurrentSH.Range("D4").Value Then
            EndingEmpCol = EndColLoop
            Exit For
        End If
    Next EndColLoop
    
    CurrentSH.Range(Cells(EmpRow, StartingEmpCol), Cells(EmpRow, StartingEmpCol)).FormulaArray = _
    "=IF(SUMPRODUCT(--(($D$2=$B9)*(E$7>=$D$3)*(E$7<=$D$4)*(NOT(E$6>=1))*(NOT(E$8='الجمعة'))))=1;VLOOKUP($D$5;LeavesTypes;2;0);"""")"
          
End Sub

我试图放置公式本身或结果是否无关紧要。

标签： excelvbaarray-formulas

解决方案

您需要将单引号 (') 替换为双引号 ("")。但是，您需要将引号加倍...

(NOT(E$8=""الجمعة""))

顺便说一句，没有必要SUMPRODUCT。以下应该足够了...

.Formula = "=IF(($D$2=$B9)*(E$7>=$D$3)*(E$7<=$D$4)*(NOT(E$6>=1))*(NOT(E$8=""الجمعة""))=1;VLOOKUP($D$5;LeavesTypes;2;0);"""")"

推荐阅读