Recognitions:
Gold Member

## 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:
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

 PhysOrg.com science news on PhysOrg.com >> Hong Kong launches first electric taxis>> Morocco to harness the wind in energy hunt>> Galaxy's Ring of Fire
 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.
 Recognitions: Gold Member I agree with Ronnin. I would do it this way: Code: 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.

Recognitions:
Gold Member

## 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:
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.

 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: 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
 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.