我正在编写一个VBA程序,该程序将允许我挖掘一组Excel数据并提取相关信息,然后将这些信息复制到另一张工作表中。
我一直在努力使搜索的单词以黄色突出显示,但我的程序不断抛出"编译错误-Ubound上的预期数组"。
Option Compare Text
Public Sub Textchecker()
'
' Textchecker
'
' Keyboard Shortcut: Ctrl+h
'
Dim Continue As Long
Dim findWhat As String
Dim LastLine As Long
Dim toCopy As Boolean
Dim cell As Range
Dim item As Long
Dim j As Long
Dim sheetIndex As Long
Dim inclusion As String
sheetIndex = 2
Continue = vbYes
Do While Continue = vbYes
findWhat = CStr(InputBox("What word would you like to search for today?"))
inclusion = CStr(InputBox("Do you have any inclusions? Separate words with commas"))
LastLine = ActiveSheet.UsedRange.Rows.Count
If findWhat = "" Then Exit Sub
j = 1
For item = 1 To LastLine
If UBound(inclusion) >= 0 Then
For Each cell In Range("BY1").Offset(item - 1, 0) Then
For Each item In inclusion
If InStr(cell.Text, findWhat) <> 0 And InStr(cell.Text, inclusion) <> 0 Then
findWhat.Interior.Color = 6
toCopy = True
Else
For Each cell In Range("BY1").Offset(item - 1, 0) Then
If InStr(cell.Text, findWhat) <> 0 Then
findWhat.Interior.Color = 6
toCopy = True
End If
Next item
End If
Next
If toCopy = True Then
Sheets(sheetIndex).Name = UCase(findWhat) + "+" + LCase(inclusion)
Rows(item).Copy Destination:=Sheets(sheetIndex).Rows(j)
j = j + 1
End If
toCopy = False
Next item
sheetIndex = sheetIndex + 1
Continue = MsgBox(((j - 1) & " results were copied, do you have more keywords to enter?"), vbYesNo + vbQuestion)
Loop
End Sub
我在这里做错了什么?
在您的代码中,inclusion
被声明为String
变量,并包含String
,尽管String
用逗号分隔。Ubound
函数适用于数组。
修复方法:使用Split
函数将字符串转换为数组。请参阅下面的示例以获得一些快速帮助,如果您需要更多详细信息,请告诉我们。
Sub Tests()
Dim inclusion() As String
inclusion = Split("One, Two, Three", ",")
MsgBox (UBound(inclusion))
End Sub
回答您的最后一条评论。
For Each中的变量必须是Object或Variant类型。
若要更改变量中的"item",请将"Dim item As Long"替换为"Dim item As Variant",甚至替换为"Dim item"因为声明的变量没有类型是变量。