Excel VBA Run Time Error '13' Type MisMatch?

[Saladsamurai]

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:

Option Explicit
Option Base 1Private 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)?

Thanks

 

[Ronnin]

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.

 

[TurtleMeister]

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

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.

 

[TurtleMeister]

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.

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.

 

[robertsca]

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.

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

 

[harborsparrow]

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.

 

[shijobaby]

http://accessaidprogramming.blogspot.com/2009/12/type-mismatch-error-vbavbms-access.html