CODEWITHSUNDEEP
vbaexcelruntime-errormsgbox
Lbrin
Lbrin

Reputation: 3

VBA runtime error 13 using Msgbox

I'm very new to VBA and only have a basic level of knowledge.

I have been trying to create a macro to cross-reference data on one sheet against multiple other sheets within the same work book. If a record is found I would like a msgbox to appear to alert the user of the location of the data.

After many hours searching the internet and piecing together bits of code this is what I have

Sub search()
Dim ws As Worksheet, found As Range
Dim TextToFind(1 To 20) As String
Dim iText As Long

TextToFind(1) = "Jade Smith"
TextToFind(2) = "Bob Collins"
TextToFind(3) = "Jemima Smythe"

For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name <> "Blacklisted Candidates" Then 'Do not search blacklist candidates!
            iText = 1
            Do While iText <= UBound(TextToFind)
                If TextToFind(iText) <> "" Then 'Do not search blank strings!
                    Set found = .UsedRange.Find(what:=TextToFind(iText), LookIn:=xlformulas, LookAt:=xlPart, MatchCase:=False)

                    If Not found Is Nothing Then
                        MsgBox "Proxy Candidate Found at " & found.Address
                    Else
                        MsgBox "No Proxy Candidates Found ", vbOKOnly, "Success!"
                    End If
                    iText = iText + 1
                End If
            Loop
        End If
   End With
Next ws

End Sub

This code however doesn't find the values from other sheets.

when testing this I just get the msgbox when no data has been found even though there is test data there.

I have a workbook of approx 9 sheets (ever growing) and I want to search the first 9 columns of each work book for the specified data which as you can see I have manually input into the macro but when running the macro I get no results returned even though there is data to find.

Upvotes: 0

Views: 1016

Answers (2)

user3598756
user3598756

Reputation: 29421

edited to account for searching in cell whose content derives from a formula

to both summarize all what has been already pointed out in comments and litelite answer and add some 0.02 cents, here a working code

Option Explicit

Sub search()
    Dim ws As Worksheet, found As Range
    Dim TextToFind(1 To 20) As String
    Dim iText As Long

    TextToFind(1) = "xxxx"
    TextToFind(2) = "xxxx"
    TextToFind(3) = "xxxxx"

    For Each ws In ThisWorkbook.Worksheets
        With ws
            If .name <> "Blacklisted Candidates" Then 'Do not search blacklist candidates!
                iText = 1
                Do While iText <= UBound(TextToFind)
                    If TextToFind(iText) <> "" Then 'Do not search blank strings!
                        Set found = .UsedRange.Find(what:=TextToFind(iText), LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

                        If Not found Is Nothing Then
                            MsgBox "Proxy Candidate Found at " & found.Address
                        Else
                            MsgBox "No Proxy Candidates Found ", vbOKOnly, "Success!"
                        End If
                        iText = iText + 1
                    End If
                Loop
            End If
       End With
    Next ws
End Sub

Upvotes: 0

litelite
litelite

Reputation: 2851

You are trying to use the binary operator And on two strings. You probably meant to use & instead to concatenate strings.

Documentation :

(The docs are for VB.Net, but they work the same in both languages)

So to fix it, replace 

MsgBox ("Proxy Candidate Found at " And rngX.Address)

By 

MsgBox ("Proxy Candidate Found at " & rngX.Address)

Upvotes: 4

Related Questions