Solved: Reading one cell value into an array

[X10A]

Hi,

I am trying to pass cell contents into an array. However, I can't seem to get the code to work if the range contains only one cell.

This is my code:

Dim MyArray as Variant

' Read the data into the variant
MyArray = Range("A1:A2").Value

MsgBox UBound(MyArray)


However, the same code will fail if the range contains only one cell:

Dim MyArray as Variant

' Read the data into the variant
MyArray = Range("A1").Value

MsgBox UBound(MyArray)


Please kindly advise what is wrong with my code. Thank you.

Reason of Edit: Code amendment

[Bob Phillips]

VBA will automatically try to sub-type a variant type variable. When it is a range assigned to that variable, it sub-types it variant as well, but when it is just a single cell, it can easisly deduce the data type, so it sub-types the variant as that data type.

[dcraker]

disregard this post if this isn't an issue,
but do you have Option Explicit in the module and is x defined?
didn't see it say here, just thinking out loud

[Dr.K]

Yep, well said.

It helped me a lot when I figured out that in some ways, VBA treats an Excel Cell just like a Variant: it can contain any kind of data, but only one kind of data at a time. And if you don't specifically tell it what kind of data it is, it guesses for you.

Oh course, you can't put an entire array into a cell, so the analogy only goes so far.

[X10A]

So sorry. The variable x is supposed to be MyArray. I have already reflect the changes in the code in my initial post.

Hi dcracker, I did not use Option Explicit in my code.

[Norie]

Have you looked at IsArray?



Hi dcracker, I did not use Option Explicit in my code.
Well you probably should.:)

[X10A]

Hi Norie,
I afraid I do not know how to apply "IsArray" to make the code work.

What I wanted is to read the cells values into an array, multiply it by 2, and pass it back to the cells.


Sub Code1()
Dim MyArray As Variant
' Read the data into the variant
MyArray = Range("A1:A2").Value
For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
MyArray(r, c) = MyArray(r, c) * 2
Next c
Next r
' Pass it back to the cells
Range("A1:A2") = MyArray
End Sub



Sub Code2()
Dim MyArray As Variant
' Read the data into the variant
MyArray = Range("A1").Value
For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
MyArray(r, c) = MyArray(r, c) * 2
Next c
Next r
' Pass it back to the cells
Range("A1") = MyArray
End Sub


Code1 and Code2 is the same except in Code2, the range contains only one cell.

Code2 will fail with a "Type Mismatch" error for the variable "MyArray". This is the part that confuses me. Must I have at least 2 cells involved for the code to work?

[dcraker]

in code 2, you are only doing one cell, what would be the purpose of having it do for each cell that it isn't doing? This works for me, if that is all you are doing. I know there is probably more to it, but I don't know what it is.Sub Code2()
Range("A1") = Range("A1").Value * 2
End Sub

[Bob Phillips]

Have you looked at IsArray?

Well you probably should.:)

How is that going to help? Knowingly why it isn't variant sub-type is enoug to tell you that you can test the range before trying to stuff it in as an array.

[Bob Phillips]

Sub Code2()
Dim MyArray As Range
Dim cell As Range
' Read the data into the variant
Set MyArray = Range("A1:B2")
For Each cell In MyArray
cell.Value = cell.Value * 2
Next cell
End Sub

[mikerickson]

Sub test2()
With Range("A1:A2")
.Value = Evaluate("=" & .Address & "*2")
End With
End Sub

[X10A]

Hi xld and dcracker,
Speed is the reason why I'm using an array instead of looping through each cell in selection. In addition, I would like to use the code on multiple selection as below.


Sub Increase2X()
Dim MyArray As Variant, Rng As Range, A As Range

Set Rng = Range("A1:D5,A8,C8,A10:D10")

For Each A In Rng.Areas
' Read the data into the variant
MyArray = A.Value

For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
MyArray(r, c) = MyArray(r, c) * 2
Next c
Next r

' Pass it back to the cells
A = MyArray
Next A

End Sub


When the above code is processing [A8], Excel will generate a "Type Mismatch" error. I would be processing quite a large selection so I thought using an array will be faster. If it can't be done, then I have no choice but to go back to looping each cell in selection. Thanks.

[Bob Phillips]

Sub Increase2X()
Dim MyArray As Variant, Rng As Range, A As Range
Dim r As Long, c As Long

Set Rng = Range("A1:D5,A8,C8,A10:D10")

For Each A In Rng.Areas
' Read the data into the variant
If A.Rows.Count <> 1 Or A.Columns.Count <> 1 Then

MyArray = A.Value

For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
MyArray(r, c) = MyArray(r, c) * 2
Next c
Next r

' Pass it back to the cells
A = MyArray
Else
A.Value = A.Value * 2
End If
Next A

End Sub

[Bob Phillips]

Or using Mike's suggestion




Sub Increase2X()
Dim A As Range

Set Rng = Range("A1:D5,A8,C8,A10:D10")

For Each A In Rng.Areas

With A
.Value = Evaluate("=" & .Address & "*2")
End With
Next A

End Sub

[X10A]

Hi,

I would like to thank all who have participated in this thread, esp xld and dcracker.

Regards.