Type Mismatch(Error13): find a string in excel sheet and store cell address in array

[andy1989]

I am trying to find a string in excel sheet and store the string cell address in an array. This string appears several times. By storing every cell address where the string is located, im checking if the string is located one below another (in row) or one beside another (in column). But I am having a type mismatch error 13, please someone help me. Code is as following:

Sorry for such a long code:

Sub Find()
 
    Dim find1, find2, find3, find4 As Range
    Dim FirstFound As String
    Dim i As Integer
    Dim y(), z() As Long
 
    i = 1
    Application.FindFormat.Clear
    Set find1 = Cells.Find(What:=Trim("Amplifier type"), _
            After:=Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not find1 Is Nothing Then
            'if found, remember location
            FirstFound = find1.Address
            'y(i) = cl.Row
            'z(i) = cl.Column
            Do
                find1.Font.Bold = True
                find1.Interior.ColorIndex = 3
                Set find1 = Cells.FindNext(After:=find1)
                ReDim Preserve y(i)
                ReDim Preserve z(i)
                y(i) = find1.Row
                z(i) = find1.Column
                i = i + 1
                Loop Until FirstFound = find1.Address
        End If
 
    For i = 1 To UBound(y)
        If ((y(i) = y(i + 1)) & (z(i + 1) - z(i) = 1)) Then
        MsgBox "Horizontal"
        End If
        If ((z(i) = z(i + 1)) & (y(i + 1) - y() = 1)) Then
        MsgBox "Vertical"
        Next i
 
 
End Sub

Some one let me know even if there is a better solution.
Thank you.

[zmbd]

andy1989:

Your first step is to put a "Stop" command at line #7 and step thru your code until you find which line it is erroring out on. Then take a look at your data and that line...

If you like, I can merge your two posts and delete the duplicate code.

Simply stating that your code "doesn't work," and posting code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.

[andy1989]

yeah sure zmbd.. nd thanks for your reply.

[zmbd]

Just noticed line 6:
Dim y(), z() As Long
Depending on which version of VB/VBA you are using, this might be your issue. With the latest release, that construct is nolonger allowed in that what you get is "y()" as a variant and "z()" as Long type cast.

Instead the new construct requires the explicit declaration so that you now have:
Dim y() As Long, z() As Long

You should make that change. Sorry I missed that the first time around... I'm still used to that being "OK" in the older versions.

[ADezii]

1. Personally, I would take a totally different approach on the Basic Concept for finding a String within a specified Range, and it is:
   

   Dim r1 As Excel.Range
   Dim r2 As Excel.Range
   Const conSTING_TO_FIND As String = "Test"       'String to find
    
   Set r1 = Range("A1:M37")    'Range conSTING_TO_FIND may be found in
    
   For Each r2 In r1
     With r2
       If .Value = conSTING_TO_FIND Then
         'Redimension an Array to contain Cell Addressess containing String,
         'then populate said Array
         Debug.Print "[" & conSTING_TO_FIND & "] found at Address " & .Address
       End If
     End With
   Next

2. OUTPUT:
   

   [Test] found at Address $M$1
   [Test] found at Address $A$6
   [Test] found at Address $L$7
   [Test] found at Address $F$13
   [Test] found at Address $K$25
   [Test] found at Address $B$31
   [Test] found at Address $H$34
   [Test] found at Address $A$37
   [Test] found at Address $M$37

3. The populated Array can be analyzed as so desired.

[andy1989]

Thanks a lot guys i got the error I was having.
It was in the line

If ((y(i) = y(i + 1)) & (z(i + 1) - z(i) = 1)) Then

Instead of '&' it should be 'And'.

Thanks once again. :)