CODEWITHSUNDEEP
vbaexcel
wesree
wesree

Reputation: 57

Highlighting word excel

I am writing a VBA program that will allow me to mine through a set of Excel data and pull out relevant information which is then copied to another sheet.

I keep trying to make it so that the word that is being searched for is highlighted in yellow, however my program constantly throws "Compile error - expected array on 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

What am I doing wrong here?

Upvotes: 0

Views: 58

Answers (2)

Fran&#231;ois Bonnefoi
Fran&#231;ois Bonnefoi

Reputation: 99

To answer your last comment.

A variable in For Each must be of type Object or Variant.

To change your 'item' in a Variant, replace 'Dim item As Long' by 'Dim item As Variant', or even by 'Dim item' as a variable declared without a type is a Variant.

Upvotes: 0

basodre
basodre

Reputation: 5770

In your code, inclusion is declared as a String variable, and contains a String, albeit a String separated by commas. The Ubound function works on arrays.

To fix: Convert the string into an array using the Split function. See the below example for some quick help, and let us know if you need more details.

Sub Tests()
    Dim inclusion() As String

    inclusion = Split("One, Two, Three", ",")

    MsgBox (UBound(inclusion))
End Sub

Upvotes: 1

Related Questions