VBA runtime error 13 using Msgbox

Question

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.

modify *MsgBox ("Proxy Candidate Found at " And rngX.Address) to MsgBox "Proxy Candidate Found at " & rngX.Address — Shai Rado
– Shai Rado, Commented Sep 6, 2016 at 15:55
Did you omit some code? @litelite has the correct answer re. the Error 13, but your Do loop doesn't have any way to exit. Also If Rng Is found Then should be If Not Rng Is Nothing Then. — Comintern
– Comintern, Commented Sep 6, 2016 at 16:03
@Comintern There is a goto in the if which he uses to break out of the infinite loop — litelite
– litelite, Commented Sep 6, 2016 at 16:09
@Comintern true, i read that quickly. His code will never end... — litelite
– litelite, Commented Sep 6, 2016 at 16:12

litelite · Accepted Answer · 2016-09-06 16:02:01Z

4

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

Documentation :

And
&

(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)

edited Sep 6, 2016 at 16:02

answered Sep 6, 2016 at 15:55

litelite

2,8464 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

The parentheses are superfluous. And they can bite you in the rear end, too - see this is confusing, why not just always use parentheses?.

Vincent G Over a year ago

Office VBA documentation for & is here and for And it's here

user3598756 · Accepted Answer · 2016-09-07 14:31:18Z

0

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

edited Sep 7, 2016 at 14:31

answered Sep 6, 2016 at 17:22

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

7 Comments

Lbrin Over a year ago

Thanks guys, I now no longer get the error message. however I have tested this after inserting a text string manually that I am searching for. although it doesn't actually find the text string. can any of you help?

user3598756 Over a year ago

more info needed: update your question with examples of minimal set of data reproducing the issue. moreover specify if cells to be searched for hold constant values or formulas results

Lbrin Over a year ago

I have updated the question now if anyone has any ideas it would be appreciated! thank you for your help so far!

user3598756 Over a year ago

I saw in you edited question that cells to be searched in host a concatenation formula. therefore see edited answer where I just substituted xlValues with xlFormulas.

user3598756 Over a year ago

@Lbrin, did you get through it?

|

Collectives™ on Stack Overflow

VBA runtime error 13 using Msgbox

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related