我想计算数组txt中"c"的数量。从1到0的转换意味着一个新的周期,这就是为什么每当这种情况发生时,我都会用"c"。在这段代码中,当我尝试计数"c"时,我得到了类型不匹配。谢谢你的帮助。
Sub CopyColumn()
Dim finalrow As Long
Dim i As Variant
ReDim arrayciclos(0)
Dim str As String
Dim ciclos As Variant
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To finalrow
arrayciclos(UBound(arrayciclos)) = Range("J" & i)
ReDim Preserve arrayciclos(UBound(arrayciclos) + 1)
Next i
For i = LBound(arrayciclos) To UBound(arrayciclos)
txt = txt & arrayciclos(i) ' & vbCrLf
Next i
MsgBox txt
Do While InStr(txt, "10")
txt = Replace(txt, "10", "c")
Loop
MsgBox txt
ciclos = 0: i = 0
For i = 0 To finalrow
If txt(i) = "c" Then ' i have Type Mismatch here
ciclos = ciclos + 1
End If
Next
MsgBox (ciclos)
End Sub
根据发布的代码,txt
变量没有显式声明。此外,当您使用第二个循环将值与txt = txt & arrayciclos(I)
连接在一起时,变量的隐式声明将成为一种字符串类型。您应该修改代码以包含Option Explicit
语句来检查未声明的变量。此外,您应该使用Mid
函数来检查最后一个循环中的"c"字符。
Option Explicit ' <-- added to check for undeclared variables
Sub CopyColumn()
Dim finalrow As Long
Dim i As Variant
ReDim arrayciclos(0)
Dim str As String ' <-- unused variable problaby should have been named txt in your example
Dim ciclos As Variant
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
finalrow = 9
For i = 2 To finalrow
arrayciclos(UBound(arrayciclos)) = Range("J" & i)
ReDim Preserve arrayciclos(UBound(arrayciclos) + 1)
Next i
For i = LBound(arrayciclos) To UBound(arrayciclos)
str = str & arrayciclos(i) ' & vbCrLf
Next i
MsgBox str
Do While InStr(str, "10")
str = Replace(str, "10", "c")
Loop
MsgBox str
ciclos = 0: i = 0
For i = 0 To finalrow
If Mid(str, i + 1, 1) = "c" Then ' <-- changed to Mid(string, start, len)
ciclos = ciclos + 1
End If
Next
MsgBox (ciclos)
End Sub
我认为您使用了太多循环。如果你想循环通过数组txt来计算"c"的数量,那么你只需要做
Dim MyArray As Variant
dim count as integer
'specify your range. For example
MyArray = Range("BS27:DS50000")
'Loop through and find all occurrences of "c"
For i = LBound(MyArray) To UBound(MyArray)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
If MyArray(i, j) = "c" Then count = count + 1
Next j
Next i
msgbox count
如果"c"在每个单元格中不是单个的,则将If MyArray(i,j)="c"替换为If MyArray(i,j)="c",并将星号放在"c"的两侧,就像星号c星号一样。这将是一个字符匹配