时间戳

首页 > 解决方案 > 将错误的引用传递给数组 VBA

arrays - 将错误的引用传递给数组 VBA

问题描述

我有帮助把它放在一起，有人可以解释为什么我将错误的信息传递给数组，我认为它是因为数组当前是一个变体而不是一个字符串。我必须更改函数还是将变量从变体更改为字符串，如果是这样怎么办？非常感谢。

    Function CountString(FullString As String, PartialString As String) As Long
         CountString = UBound(Split(FullString, PartialString))
    End Function 

    Dim ws2 As Worksheet, rng As Range, totcount As Long, cell As Range, strsearch1 As Variant
    
    strsearch1 = Array("Cat", "Dog")
    
    For Each ws2 In ThisWorkbook.Worksheets
     If (ws2.Name) <> "Search Results" Then
       ws2.Activate
       ActiveSheet.Columns("H:H").EntireColumn.Delete
       ActiveSheet.Columns("H:H").NumberFormat = "0"
       ActiveSheet.Range("A1").Select
    
       Set rng = ws2.Range("C2:D10")
       For Each cell In rng
           cell.Select
           totcount = "0"
           totcount = totcount + CountString(cell.Value, strsearch1)
   
           Range("A" & (ActiveCell.Row)).Select
           ActiveCell.Offset(0, 7).Select
           ActiveCell = ActiveCell.Value + totcount
   
       Next cell
     End If
   Next ws2

标签： arraysexcelvbastringvariables

解决方案

我在想你的代码应该是这样的：

Sub Test1()

    Dim ws2 As Worksheet, rng As Range, totcount As Long, cell As Range, strsearch1 As Variant
    Dim itm As Variant

    strsearch1 = Array("Cat", "Dog")

    For Each ws2 In ThisWorkbook.Worksheets
        If ws2.Name <> "Search Results" Then
            ws2.Columns("H:H").EntireColumn.Delete
            ws2.Columns("H:H").NumberFormat = "0"
            
            Set rng = ws2.Range("C2:D10")
            For Each cell In rng
                totcount = 0
                
                'Pass each item in the array to the function, 'Cstr' converts it to a string first.
                For Each itm In strsearch1
                    totcount = totcount + CountString(cell.Value, CStr(itm))
                    cell.Offset(0, 7) = cell.Offset(0, 7) + totcount
                Next itm
            Next cell
            
        End If
    Next ws2

End Sub

我不明白你的代码应该做什么。

除了 1 之外，是否会UBOUND(Split("x","x"))返回任何内容？x它通过分隔符拆分字符串，x这将在一个数组中给出两个空字符串 -LBOUND这将是 0，UBOUND将是 1。

推荐阅读