文章/答案/技术大牛

发布

社区首页 >问答首页 >无法在VBA中设置类范围FormulaArray的属性。

问无法在VBA中设置类范围FormulaArray的属性。
EN

Stack Overflow用户

提问于 2011-12-21 08:20:02

回答 3查看 1.8K关注 0票数 0

    Sub Parse()


        Workbooks.OpenText Filename:="C:\Users\karthic.rangaraj\Desktop\4401.csv"

         ' Parse it using comma and semicolon as delimiters
        Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=True, Space:=False, Other:=False, _
        FieldInfo:= _
        Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))


        Range("B:B,D:D").Delete
         'Range("D1").FormulaR1C1 = "Application_ID"

         Dim lLR As Long
    Dim vArray As Variant
    Dim sString As String
     '*****************************************************************************************
     'Add as many items you like to this array
     '*****************************************************************************************
    vArray = Array("Windows XP", "Adobe", "IBM", "VLC", "PDFCreator", "Sonic", "Office", "Sigamtel", "Printer")
    For i = LBound(vArray) To UBound(vArray)
        sString = sString & Chr(34) & vArray(i) & Chr(34) & ","
    Next i
     '*****************************************************************************************
     'This is the final array string that we pass to array formula
     '*****************************************************************************************
    sString = "{" & Left(sString, Len(sString) - 1) & "}"
    lLR = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2").FormulaArray = "=INDEX(" & sString & ",1,MATCH(1,--ISNUMBER(SEARCH(" & sString & ",$C2,1)),0))"


    Range("D2").AutoFill Destination:=Range("D2:D" & lLR)
ActiveWorkbook.SaveAs "C:\Users\karthic.rangaraj\Desktop\4401.xls", FileFormat:=56
' 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (workbook with macro's in 2007)

    End Sub

我可以使用以下代码将这个单个文件保存为.xls文件

  ActiveWorkbook.SaveAs "C:\Users\karthic.rangaraj\Desktop\4401.xls", FileFormat:=56
    ' 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (workbook with macro's in 2007)

以下是我的问题：

如何访问文件夹C:\Users\karthic.rangaraj\Desktop\CSVFiles中的一堆CSV文件&在使用VBA？
以相同的文件名完成处理后，将其保存为同一个文件夹中的XLS文件--在数组中有问题：

代码：

vArray = Array("Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML")
        For i = LBound(vArray) To UBound(vArray)
            sString = sString & Chr(34) & vArray(i) & Chr(34) & ","
        Next i

sString = "{" & Left(sString, Len(sString) - 1) & "}"
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").FormulaArray = "=INDEX(" & sString & ",1,MATCH(1,--ISNUMBER(SEARCH(" & sString & ",$C2,1)),0))"
Range("D2").AutoFill Destination:=Range("D2:D" & lLR)

如果输入超过10项(假设是14项)，我将收到以下错误消息：

Unable to set the property of the class range FormulaArray

这里的数组公式怎么了？

arrays

excel

vba

csv

回答 3

Stack Overflow用户

回答已采纳

发布于 2011-12-21 11:42:43

那么，这个简单的公式有什么问题呢？

vArray = Array("Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML")
        For i = LBound(vArray) To UBound(vArray)
            sString = sString & Chr(34) & vArray(i) & Chr(34) & ","
        Next i

sString = "{" & Left(sString, Len(sString) - 1) & "}"
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=HLOOKUP($C2," & sString & ",1,FALSE)"
Range("D2").AutoFill Destination:=Range("D2:D" & lLR)

编辑：

好了，现在我明白你想做什么了:试试下面的代码

Sub Joe()
vArray = Array("Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML")
        For i = LBound(vArray) To UBound(vArray)
            sstring = sstring & Chr(34) & vArray(i) & Chr(34) & ","
        Next i
sstring = "{" & Left(sstring, Len(sstring) - 1) & "}"
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Formula = "=InList($C2," & sstring & ")"
Range("E2").AutoFill Destination:=Range("E2:E" & lLR)
End Sub
Function InList(theCell As Range, theList As Variant)
    Dim j As Long
    Dim str As String
    InList = CVErr(xlErrNA)
    str = UCase(Trim(CStr(theCell)))
    For j = LBound(theList) To UBound(theList)
        If str Like UCase("*" & theList(j) & "*") Then
            InList = theList(j)
            Exit For
        End If
    Next j
End Function

票数 1

Stack Overflow用户

发布于 2011-12-21 11:04:48

VBA可以创建的数组公式的最大长度为255个字符。

票数 1

Stack Overflow用户

发布于 2011-12-21 11:23:58

从Charles的答案开始，您可以使用定义名称将软件列表数组放入(可选地隐藏)“命名常量”中，并使用公式中的命名数组使其更短。

 ThisWorkbook.Names.Add Name:="MyList", _
                     RefersTo:=Array(Array("Windows XP", "Adobe", "IBM")

..。

Range("D2").FormulaArray = "=INDEX(MyList,1,...

这将使您的公式长度独立于项目数。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/8586805

复制

相似问题

问无法在VBA中设置类范围FormulaArray的属性。
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法在VBA中设置类范围FormulaArray的属性。EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法在VBA中设置类范围FormulaArray的属性。
EN