PDA

View Full Version : Solved: Reading one cell value into an array



X10A
10-13-2008, 10:56 AM
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
10-13-2008, 11:10 AM
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
10-13-2008, 11:24 AM
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
10-13-2008, 11:24 AM
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
10-13-2008, 11:37 AM
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
10-13-2008, 11:40 AM
Have you looked at IsArray?



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

X10A
10-13-2008, 12:06 PM
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
10-13-2008, 12:16 PM
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
10-13-2008, 01:56 PM
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
10-13-2008, 02:00 PM
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
10-13-2008, 03:22 PM
Sub test2()
With Range("A1:A2")
.Value = Evaluate("=" & .Address & "*2")
End With
End Sub

X10A
10-13-2008, 10:32 PM
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
10-14-2008, 12:41 AM
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
10-14-2008, 12:43 AM
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
10-14-2008, 07:03 AM
Hi,

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

Regards.