Type mismatch error when counting words/text with more than 255 characters

[Nick70]

Hi,

I have a code that looks at text in a range in column B and then counts how many times this text is found in column D (see code below)

Sub FindTotal()
    Application.ScreenUpdating = False
    Dim rng As Range, total As Long
    For Each rng In Range("B4", Range("B" & Rows.Count).End(xlUp))
        total = total + WorksheetFunction.CountIf(Range("D4", Range("D" & Rows.Count).End(xlUp)), rng)
    Next rng
    MsgBox total
    Application.ScreenUpdating = True
End Sub

I have found that if a text in column B is more than 255 letters (so 256 letters or more) then the code does not work and gives and gives a Run-time error '13': Type mismatch.

Does anybody know how I can fix the code so that it also counts text with more then 255 letters and I no longer get the type mismatch error?

Thanks,
Nic

 

[duggie33]

Hi Nic,

On your worksheet you could try a formula like this...or you could possibly adapt it to VBA.

=COUNTA(FILTER($D$4:$D$80,ISNUMBER(SEARCH(B4,$D$4:$D$80,1))))

I tested on a very small sample and it seemed to work for long strings.

Hope that helps,

Doug

 

[Nick70]

Thanks Doug but when I tried it it still stops at 255 characters after that it stops counting.

 

[duggie33]

Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.

Thanks,

Doug

 

[duggie33]

You could try to use SUMPRODUCT like below...

Book_2023-09-10.xlsm
	B	C	D	E	F	G
2	Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	353	Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	353		2
3	please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	345	please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	345		4
4			Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	353
5			please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	345
6			please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	345
7			please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information. Can you please provide some sample data? If you can, use the XL2BB tool to share the data. That way we can copy and paste it into our Excel. and work with the same information.	345
Sheet10

Cell Formulas
Range	Formula
C2:C3,E2:E7	C2	=LEN(B2)
G2:G3	G2	=SUMPRODUCT(--($D$2:$D$7=$B2))

Hope that helps,

Doug