View Full Version : Solved: Reading one cell value into an array
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
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.
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.:)
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
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
Hi,
I would like to thank all who have participated in this thread, esp xld and dcracker.
Regards.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.