Runtime Error 13. Type MisMatch for Variant Array of 2-Dimensions

[Frankie Carbone]

Hi Guys. I am trying to learn VBA and am fairly new to the code (I do program in C, Java, and a few other obscure languages). I have spent a few hours on this issue and I am at my wit's end. I am trying to copy the contents of a range into an array of type variant because I have to redim the array. The array's length is equal to the number of rows of data, and the number of columns of the row with the MOST cells that contain data. That is, the rows are not all of equal length. Some have 4 cells of data, some 6, some 5, some x, ect.. Also, the last cell of each row contains a statement that starts with "$$". This is my comment indicator from the text file where I import the data. Things are going OK until I get here.... Here's the code.

Comments are in italics.
---------------------------------------------------------------------------------------------
Option Explicit

'Need an array to hold the data
Dim DataArray() As Variant


Private Sub UserForm_Initialize()

Call ReadTextFromAddModVoltageRegulatorTXTFile
​

End Sub



Sub ReadTextFromAddModVoltageRegulatorTXTFile()

'
Need counters
Dim counter1, counter2 As Long
counter1 = 0
counter2 = 0

'Dimension 1 for the array to hold the data
Dim Dimension1 As Integer
Dimension1 = 0

'Dimension 2 for the array to hold the data
Dim Dimension2 As Integer


'Declare a variable that can hold a reference to a file
Dim fso As Scripting.FileSystemObject

'Create a FileSystemObject
Set fso = New Scripting.FileSystemObject

'Declare a variable that can hold a reference to a text file
Dim ts As Scripting.TextStream

'Opens the text file for adding data to it.
Set ts = fspenTextFile("C:\Power_SW\Form_Data\AddModVoltageRegulator.txt")

'Selects a temporary worksheet, a scratchpad so to speak, to copy the data to
Worksheets("Temp1").Select
Range("A1").Select


'Do loop that reads data until end of file. "AtEndOfStream" is the method that returns an end of file.

Do Until ts.AtEndOfStream
​

'After the last tab, a word still remains. We need to capture that to complete the harvesting of the line
ActiveCell.Value = ts.ReadLine

'Move down one cell and slide BACK all the way to the left because you kept offsetting columns in the loop
ActiveCell.Offset(1, 0).Select
Dimension1 = Dimension1 + 1
​

Loop

'This method converts all of the text to columns ALL AT ONCE
'Take column "A" and convert all text to columns using TAB as the delimiter

Range("A:A").TextToColumns Tab:=True
​

​

'Go back to the start of the data
Range("A1").Select
​

'reset the counters to be safe
counter1 = 0 counter2 = 0

'Redim the array based on the number of rows of data (Dimension1) and the MAX number of columns '(Dimension2)
ReDim DataArray(1, Dimension1, 1 To Dimension2)
​

'Loop until the first cell in the row being evaluated is empty (no more rows)

Do Until IsEmpty(ActiveCell.Value)

​

'Loop until the last cell in the column is a comment, delineated by the start of '"$$"
​

Do Until Left(ActiveCell.Value, 2 <> "$$")
​

DataArray(counter1, counter2) = Range("A1").Offset(counter1, counter2).Value
counter2 = counter2 + 1
​

Loop '(inner loop)
​

​

'Move down a row, then move the column back to the starting point (Column A)
counter1 = counter1 + 1
counter2 = 0

​

Loop ('outer loop)
​

........... and so on and so forth...Code never gets beyond bolded line.................

​

When I F8 to this line (bolded above) I get "Run time error 13" Type Mismatch


DataArray(counter1, counter2) = Range("A1").Offset(counter1, counter2).Value


I've tried changing .value to .text. No dice. I've tried Cstr the expression on the right. Nada. made the counters integer, then long. Nothing.

I tried this code shown above after the more elegant expression I put in also failed with the same error. That single line expression was (in bold below).

'Assigns the array all at once!
DataArray = ThisWorkbook.Worksheets("Temp1").Cells(Dimension1, Dimension2).Value

So, I am stuck. Please advise and thank you in advance! I have searched this site and the web for an answer but I am stuck.

 

[Frankie Carbone]

Hi Guys. I am trying to learn VBA and am fairly new to the code (I do program in C, Java, and a few other obscure languages). I have spent a few hours on this issue and I am at my wit's end. I am trying to copy the contents of a range into an array of type variant because I have to redim the array. The array's length is equal to the number of rows of data, and the number of columns of the row with the MOST cells that contain data. That is, the rows are not all of equal length. Some have 4 cells of data, some 6, some 5, some x, ect.. Also, the last cell of each row contains a statement that starts with "$$". This is my comment indicator from the text file where I import the data. Things are going OK until I get here.... Here's the code.

Comments are in italics.
---------------------------------------------------------------------------------------------
Option Explicit

'Need an array to hold the data
Dim DataArray() As Variant


Private Sub UserForm_Initialize()

Call ReadTextFromAddModVoltageRegulatorTXTFile
​

End Sub



Sub ReadTextFromAddModVoltageRegulatorTXTFile()

'
Need counters
Dim counter1, counter2 As Long
counter1 = 0
counter2 = 0

'Dimension 1 for the array to hold the data
Dim Dimension1 As Integer
Dimension1 = 0

'Dimension 2 for the array to hold the data
Dim Dimension2 As Integer


'Declare a variable that can hold a reference to a file
Dim fso As Scripting.FileSystemObject

'Create a FileSystemObject
Set fso = New Scripting.FileSystemObject

'Declare a variable that can hold a reference to a text file
Dim ts As Scripting.TextStream

'Opens the text file for adding data to it.
Set ts = fspenTextFile("C:\Power_SW\Form_Data\AddModVoltageRegulator.txt")

'Selects a temporary worksheet, a scratchpad so to speak, to copy the data to
Worksheets("Temp1").Select
Range("A1").Select


'Do loop that reads data until end of file. "AtEndOfStream" is the method that returns an end of file.

Do Until ts.AtEndOfStream
​

'After the last tab, a word still remains. We need to capture that to complete the harvesting of the line
ActiveCell.Value = ts.ReadLine

'Move down one cell and slide BACK all the way to the left because you kept offsetting columns in the loop
ActiveCell.Offset(1, 0).Select
Dimension1 = Dimension1 + 1
​

Loop

'This method converts all of the text to columns ALL AT ONCE
'Take column "A" and convert all text to columns using TAB as the delimiter

Range("A:A").TextToColumns Tab:=True
​

​

'Go back to the start of the data
Range("A1").Select
​

'reset the counters to be safe
counter1 = 0 counter2 = 0

'Finds the row with the most columns and makes this dimension2
Do Until IsEmpty(ActiveCell.Value)

counter1 = Range(ActiveCell.Address, Range(ActiveCell.Address).End(xlToRight)).Cells.Count
If Dimension2 < counter1 Then Dimension2 = counter1
'Each row adds 1 to Dimension1
Dimension1 = Dimension1 + 1

'Next row
ActiveCell.Offset(1, 0).Select
Loop

'Sloppy way to do this. , work on this because line 1001 has an error - see below, it's commented out.

'Put us back at A1, reset the counters to be safe
Range("A1").Select
counter1 = 0
counter2 = 0

'Redim the array based on the number of rows of data (Dimension1) and the MAX number of columns '(Dimension2)
ReDim DataArray(1, Dimension1, 1 To Dimension2)
​

'Loop until the first cell in the row being evaluated is empty (no more rows)

Do Until IsEmpty(ActiveCell.Value)

​

'Loop until the last cell in the column is a comment, delineated by the start of '"$$"
​

Do Until Left(ActiveCell.Value, 2 <> "$$")
​

DataArray(counter1, counter2) = Range("A1").Offset(counter1, counter2).Value
counter2 = counter2 + 1
​

Loop '(inner loop)
​

​

'Move down a row, then move the column back to the starting point (Column A)
counter1 = counter1 + 1
counter2 = 0

​

Loop ('outer loop)
​

........... and so on and so forth...Code never gets beyond bolded line.................

​

When I F8 to this line (bolded above) I get "Run time error 13" Type Mismatch


DataArray(counter1, counter2) = Range("A1").Offset(counter1, counter2).Value


I've tried changing .value to .text. No dice. I've tried Cstr the expression on the right. Nada. made the counters integer, then long. Nothing.

I tried this code shown above after the more elegant expression I put in also failed with the same error. That single line expression was (in bold below).

'Assigns the array all at once!
DataArray = ThisWorkbook.Worksheets("Temp1").Cells(Dimension1, Dimension2).Value

So, I am stuck. Please advise and thank you in advance! I have searched this site and the web for an answer but I am stuck.

The code that is UNDERLINED is also part of the subroutine and was there when the error happened, I am just a bit tired and accidentally deleted it when I typed the first post.

So PLEASE USE THIS CODE instead.

Thanks,