CubsFan
01-22-2016, 01:05 PM
I am trying to assign a dynamic range of cell values to an array. In that effort, I discovered one of my errors was how I dim the array, but I do not understand the reason why my original method does not work.
The pertinent portion of my original code was as below but I received a type mismatch.
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = rngInput
...
End With
...
end sub
However, both of the following work:
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
End With
...
end sub
AND
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = rngInput
...
End With
...
end sub
So, what gives? If I dim arrayInput with "()" AND I set the array using the .Range method, it works but by storing the range to a variable first does not? But, if I dim without "()" it works? I obviously am not understanding the declaration of the array fully.
Can someone help me understand why dim arrayName()... arrayName = Range(x:y) works but arrayName = rngVariable does not? Thank you!
CubsFan
The pertinent portion of my original code was as below but I received a type mismatch.
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = rngInput
...
End With
...
end sub
However, both of the following work:
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput()
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
End With
...
end sub
AND
Sub DataConversion()
Dim rngInput, rngOutput As Range
Dim LastInputRow, LastInputColumn, i, j, counter As Integer
Dim arrayInput
With Sheets("Copy of Source Data")
LastInputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
...
Set rngInput = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
...
arrayInput = rngInput
...
End With
...
end sub
So, what gives? If I dim arrayInput with "()" AND I set the array using the .Range method, it works but by storing the range to a variable first does not? But, if I dim without "()" it works? I obviously am not understanding the declaration of the array fully.
Can someone help me understand why dim arrayName()... arrayName = Range(x:y) works but arrayName = rngVariable does not? Thank you!
CubsFan