Excel VBA:: Run Time Error '13' Type MisMatch?

  1. Excel VBA:: Run Time Error '13' Type MisMatch??

    I keep getting this error when I run this code. Nothing is highlighted..and VBA help is not working out for me.

    It just started when I added the for loop in bold below:


    Code (Text):
    Option Explicit
    Option Base 1


    Private Sub MathML_Converter()
    Dim i As Integer
    Dim j As Integer
    Dim NumElements As Integer
    Dim Text As String
    Dim MyArray() As String
    Dim MyNewArray() As String


    '***************************************************************
       
        'Clear ActiveSheet

        Sheets("Sheet1").Range("1:65536").ClearContents
        Open "C:\Documents and Settings\Owner\Desktop\APC_Related\MathML.txt" _
        For Input As #1


        i = 1
            Do While Not EOF(1)
                Text = Input(1, #1)
                ReDim Preserve MyArray(i)
                MyArray(i) = Text
                Cells(i, 1) = Text
                i = i + 1
       
            Loop
       
            Close #1
       
       
        NumElements = i - 1
    '    MsgBox "Number of Elements SHOULD be " & NumElements
    '    MsgBox "Number of Elements IS " & UBound(MyArray)
       
    '*******************************************************************
    [b]'Eliminate WhiteSpace From MyArray

        j = 1
        For i = 1 To NumElements
            ReDim Preserve MyNewArray(j)
            If MyArray(i) <> " " Then _
            MyNewArray(j) = MyArray(i) & _
            j = j + 1
           
        Next i[/b]
       
       
       
       
           
     
    Any ideas? Am I missing the obvious (I usually do; not my specialty:redface:)?

    Thanks
     
  2. jcsd
  3. Re: Excel VBA:: Run Time Error '13' Type MisMatch??

    I have not used any VB in a while but I would say on the 3rd line after the for it might be trying to concantenate or add the j=j+1 to the element of the array.
     
  4. Re: Excel VBA:: Run Time Error '13' Type MisMatch??

    I agree with Ronnin. I would do it this way:

    Code (Text):
    j = 1
    For i = 1 To NumElements
        ReDim Preserve MyNewArray(j)
        If MyArray(i) <> " " then
            MyNewArray(j) = MyArray(i)
            j = j + 1
        EndIf
    Next i
    However, if you insist on not using the block IF then you should leave out the ampersand and use the colon.
     
  5. Re: Excel VBA:: Run Time Error '13' Type MisMatch??

    I noticed that you redim preserve the array on each pass of the loop. Your code would run faster if you precount the number of elements required beforehand and redim the new array before starting the loop.

    Code (Text):
    j = 0
    For i = 1 to NumElements
        If MyArray(i) <> " " then
            j = j + 1
        EndIf
    next i

    ReDim MyNewArray(j)

    j = 1
    For i = 1 to NumElements
        If MyArray(i) <> " " then
            MyNewArray(j) = MyArray(i)
            j = j + 1
        EndIf
    Next i
    Even though it is more code, the run time will be faster.
     
  6. Re: Excel VBA:: Run Time Error '13' Type MisMatch??

    I get the same error when I run the following code. If I comment out the lines in bold, the error goes away, but then the code doesn't do what I want! Any suggestions? Thanks in advance for any help you can give me. I'm no VBA expert and I'm hoping I'm just missing something that's obvious to others.

    Code (Text):
    Private Sub CommandButton1_Click()
    Dim i As Integer          'row index variable
    Dim scount As Integer
    Dim score As Integer
    Dim coffset As Integer
    Dim leftcount As Integer
    Dim j As Integer          'column index variable
    Dim LastCol As Integer

    i = 4
    Do While i < 6
        coffset = 2
        scount = 0
        leftcount = 1
        With ActiveSheet
            LastCol = .Cells(i, .Columns.Count).End(xlToLeft).column
        End With
        j = LastCol
        Do While coffset < 22
            score = Cells(i, j)
            j = LastCol - leftcount
            If j > 60 Then
                [B]If score > 0 Then[/B]
                    Cells(i, coffset) = score
                    scount = scount + 1
                    coffset = 2 + scount
                [B]End If[/B]
            End If
            leftcount = leftcount + 1
        Loop
    i = i + 1
    Loop
    End Sub
     
  7. harborsparrow

    harborsparrow 401
    Gold Member

    Re: Excel VBA:: Run Time Error '13' Type MisMatch??

    In the highlighted code above, you are assuming that the value in the score variable is a number, whereas it is probably a string. You may need to convert it.
     
Know someone interested in this topic? Share a link to this question via email, Google+, Twitter, or Facebook

Have something to add?