2

I'm not great at VBA, but all of a sudden, I started getting this error. Please let me know if you can find what the error is. I'm so stuck on this one. I did search everywhere and found lots of similar posts but the solution always seems to be very specific to the code. Any of you experts would be greatly appreciated.

Here's my code:

Sub FindSimilar()
    Dim phrases As Range, phrase As Range
    Dim terms As Range, term As Range
    Dim matches As String
    Dim words() As String

    'ensure this has the correct sheet names for your workbook
    Set phrases = ThisWorkbook.Worksheets("Export").Range("B2:B3500")
    Set terms = ThisWorkbook.Worksheets("Topics").Range("D767:D967")

    For Each term In terms
        matches = ""
        words() = Split(term.Value)

        For i = 0 To UBound(words, 1)
        If Len(words(i)) > 2 Then
         Select Case words(i)
          Case "examplewords", "blacklist"
          Case Else
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
         End Select
        End If

I'm really at a loss as to why the

Run-time error 13: Type mismatch

is being thrown.

The breakpoint occurs at the following lines:

        matches = ""

And

       Set terms = ThisWorkbook.Worksheets("Topics").Range("D773:D779")

And

       For i = 0 To UBound(words, 1)
braX
  • 11,506
  • 5
  • 20
  • 33
DomainsFeatured
  • 1,426
  • 1
  • 21
  • 39
  • 2
    ... and where (what line) pray tell does the error occur on? –  Feb 23 '18 at 23:26
  • you don't need a rank for a 1-D array. `For i = LBound(words) To UBound(words)` –  Feb 23 '18 at 23:28
  • are there any worksheet error codes (e.g. #N/A, #VALUE!, etc) in either Export!B2:B3500 or Topics!D767:D967 ? –  Feb 23 '18 at 23:31
  • The error occurs at `matches = ""` – DomainsFeatured Feb 23 '18 at 23:42
  • 1
    doesn't make sense but can you get over it by swapping out `""` for `vbnullstring` ? –  Feb 23 '18 at 23:45
  • 1
    `matches` is declared as String, and `matches =""` is good. Are you sure the yellow highlited line is that one when you click Debug? – Foxfire And Burns And Burns Feb 23 '18 at 23:46
  • Besides, i guess this is not the complete code, because we don't see the end of some For sentences. Do you make anything else with `matches` later? Does the code make some loops in your For sentences or does it instant fail at first run? – Foxfire And Burns And Burns Feb 23 '18 at 23:48
  • @FoxfireAndBurnsAndBurns - yeah, I was thinking that the problem had to between the last shown End If and the Next term statement. –  Feb 23 '18 at 23:50
  • okay, I'm getting error at this line as well ` Set terms = ThisWorkbook.Worksheets("Topics").Range("D773:D779")` – DomainsFeatured Feb 23 '18 at 23:53
  • Idk why but the error breakpoint is jumping around. Now it shows at `For i = 0 To UBound(words, 1)` – DomainsFeatured Feb 23 '18 at 23:56
  • 1
    ... and you have *'ensure this has the correct sheet names for your workbook'* i.e. the workbook the code is in? –  Feb 23 '18 at 23:57
  • yes, I'm positive. – DomainsFeatured Feb 23 '18 at 23:57
  • Sounds like a corrupt project, corrupt environment or both. Save your code to notepad then reboot (cold start) the computer. If that doesn't fix it then rebuild your project from the notepad save. –  Feb 23 '18 at 23:59
  • Execute your code step by step, pressing F8 on keyboard, and take a look if the code does what you want. Tell us the first error that appears. Resto of errors can be caused by the first one. But we need to know which one is the first error – Foxfire And Burns And Burns Feb 23 '18 at 23:59
  • And if you can paste all the code, it may help because maybe your code does something later in the For...loops that causes errors on second run. – Foxfire And Burns And Burns Feb 24 '18 at 00:01
  • When I run line by line, it seems to get stuck in this loop: `For Each phrase In phrases` – DomainsFeatured Feb 24 '18 at 00:08
  • *it seems* does not sound leggit LoL. Does it does it not? do you get an error message in that line when you execute step by step? – Foxfire And Burns And Burns Feb 24 '18 at 00:11
  • Well it just stayed in that loop. I can create screen shots if you guys need. I really think this its something with `For i = 0 To UBound(words, 1)` That's where I keep getting the error now. – DomainsFeatured Feb 24 '18 at 00:13
  • see second comment above. –  Feb 24 '18 at 00:16
  • Maybe we should try **take this to chat**. I've never used it but so many comments will make this question look like a soap opera. Can the 3 of us chat? – Foxfire And Burns And Burns Feb 24 '18 at 00:17
  • Hi Jeeped, I did restart excel entirely. No change. – DomainsFeatured Feb 24 '18 at 00:18
  • Usually, it will give us the option to create a chat room. – DomainsFeatured Feb 24 '18 at 00:18
  • *it just stayed in the loop* is normal because it is a loop. So that loop works good. Does the rest of loops work at least once complete run? – Foxfire And Burns And Burns Feb 24 '18 at 00:20
  • 1
    That's not what I suggested. I stated *'reboot (cold start) the computer'*. Something like [this](https://www.youtube.com/watch?v=p85xwZ_OLX0). –  Feb 24 '18 at 00:21
  • Anyways, to debug your loops, you can try to set some breakpoints in them, so you don't have to pres f8 3000 times (Range ("B2:B3500") is a huge range). If you don't know about breakpoints, take a look [here](https://www.wiseowl.co.uk/blog/s196/breakpoints.htm) – Foxfire And Burns And Burns Feb 24 '18 at 00:23
  • Good call firefox. I ran it with only B2:B10. I'm not getting any errors and it's running through fine. Going to keep running line by line – DomainsFeatured Feb 24 '18 at 00:28
  • 1
    @FoxfireAndBurnsAndBurns - I find positioning the cursor then ctrl+f8 helpful as well. –  Feb 24 '18 at 00:28
  • Okay, so when I run with the range B2:B900, it works fine. But, when I use B2:B1000, it gives me an error. I wonder what kind of data would cause it from running – DomainsFeatured Feb 24 '18 at 00:34
  • 1
    Try with Range ("B900:B1000"). IT you get an error, means that any cell in that range got an error value. Could you check those cells in Excel? – Foxfire And Burns And Burns Feb 24 '18 at 00:37
  • Jeepeed and Foxfire, you got it. I found one single `#N/A` cell. It was not there as an error but as a pasted value :-( It's working now! – DomainsFeatured Feb 24 '18 at 00:37
  • Thanks Foxfire to lead me to this solution. – DomainsFeatured Feb 24 '18 at 00:38
  • Do either of you guys want to provide an answer, or do you think we should delete this question. I can't thank you guys enough. If I can do anything, just let me know. – DomainsFeatured Feb 24 '18 at 00:39
  • Actually @Jeeped got the answer. His second comment was *are there any worksheet error codes* *(e.g. #N/A, #VALUE!, etc) in either Export!B2:B3500 or Topics!D767:D967 ?* LoL so he/she was right from start. Omg!. But it was fun. Glad you could get this running now. He should post the answer. – Foxfire And Burns And Burns Feb 24 '18 at 00:40
  • Yea, that was a challenge. Thanks so much! – DomainsFeatured Feb 24 '18 at 00:42
  • @FoxfireAndBurnsAndBurns - There really isn't much to state beyond what has already been said but I provided two error locating methods as a response below. –  Feb 24 '18 at 01:22

1 Answers1

3

A Run-time error '13': Type mismatch halfway through looping through a large set of values collected from the worksheet is almost always due to encountering a worksheet error code.

Are there any worksheet error codes (e.g. #N/A, #VALUE!, etc) in either Export!B2:B3500 or Topics!D767:D967 ?

I found one single #N/A cell. It was not there as an error but as a pasted value :-( It's working now!

You can quickly locate any worksheet errors in a long column of values (or even an entire worksheet) with these steps.

  1. Select entire column. If you want to look at the whole worksheet, just select any single cell. 2, Tap F5 then click Special.
    enter image description here
  2. Choose Formulas and leave only Errors checked.
    enter image description here
  3. Click OK.
    enter image description here

Worksheet errors can also be found with one or both of the following.

<range>.SpecialCells(xlCellTypeConstants, xlErrors)
<range>.SpecialCells(xlCellTypeFormulas, xlErrors)
  • 1
    I would add, as a quick and easy solution, applying a filter and then filter by `#N/A ` or `#VALUE!` or `#¡DIV/0!` or `#¡REF!`. All errors are always at the bottom of the list in a filter, so it's really quick to apply a filter and check for errors if your data is thousands of rows but only got 1 error, because in the filter list, all errors appear at bottom of list. ` – Foxfire And Burns And Burns Feb 24 '18 at 01:31
  • Good point. Quick and dirty exposure of all things error. Maybe post that. I plan to point to this Q&A for duplicates. Heck, I'll up-vote it just for kicks and giggles. –  Feb 24 '18 at 01:37