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


by Saladsamurai
Tags: error, excel, mismatch, time, type
Saladsamurai
Saladsamurai is offline
#1
Aug5-09, 04:38 PM
Saladsamurai's Avatar
P: 3,012
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 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)
    
'*******************************************************************
'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
Any ideas? Am I missing the obvious (I usually do; not my specialty)?

Thanks
Phys.Org News Partner Science news on Phys.org
Review: With Galaxy S5, Samsung proves less can be more
Making graphene in your kitchen
Study casts doubt on climate benefit of biofuels from corn residue
Ronnin
Ronnin is offline
#2
Aug5-09, 07:06 PM
P: 208
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
TurtleMeister is offline
#3
Aug5-09, 07:53 PM
P: 735
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
TurtleMeister is offline
#4
Aug5-09, 08:20 PM
P: 735

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.

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
robertsca is offline
#5
Aug19-09, 11:01 AM
P: 1
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
            If score > 0 Then
                Cells(i, coffset) = score
                scount = scount + 1
                coffset = 2 + scount
            End If
        End If
        leftcount = leftcount + 1
    Loop
i = i + 1
Loop
End Sub
harborsparrow
harborsparrow is offline
#6
Sep7-09, 06:54 PM
harborsparrow's Avatar
P: 322
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
shijobaby is offline
#7
Dec3-09, 06:56 AM
P: 1
http://accessaidprogramming.blogspot...ms-access.html


Register to reply

Related Discussions
excel, error of trend line? Math & Science Software 2
Error Analysis On Excel Questions Introductory Physics Homework 2
Expectations for Type I Error Set Theory, Logic, Probability, Statistics 4
Parallax error - what type of error is it? General Physics 5
Excel help with error bar formula Set Theory, Logic, Probability, Statistics 1