Register to reply

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

by Saladsamurai
Tags: error, excel, mismatch, time, type
Share this thread:
Saladsamurai
#1
Aug5-09, 04:38 PM
Saladsamurai's Avatar
P: 3,015
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
Security CTO to detail Android Fake ID flaw at Black Hat
Huge waves measured for first time in Arctic Ocean
Mysterious molecules in space
Ronnin
#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
#3
Aug5-09, 07:53 PM
TurtleMeister's Avatar
P: 744
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
#4
Aug5-09, 08:20 PM
TurtleMeister's Avatar
P: 744
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
#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
#6
Sep7-09, 06:54 PM
PF Gold
harborsparrow's Avatar
P: 347
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
#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