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

 P: 3,016 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
 P: 759 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.
 P: 759 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.
 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