Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Array and Range

  Asked By: Kenneth    Date: Sep 28    Category: MS Office    Views: 1097
  

I tried to create a copy a range into an array, do some thing within
the array and then copy the value back into the range.(codes
attached below)

I kept getting "subscript out of range" error. Can anyone tell me
why?

BTW, array can be up to 100 dimensions. If I want to manipulate a
range with more than 100 columns, do I have to break it down? Is
there another way out?

Codes:
============================================================
Sub RangeToVariant2()
Dim x As Variant
Dim r As Integer, c As Integer, p As Integer
x = Range("A1:C5").Value
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
For p = 1 To UBound(x, 3)
If IsNumeric(x(r, c, p)) Then
x(r, c, p) = x(r, c, p) * 2
End If
Next p
Next c
Next r
Range("A1:C5") = x
End Sub

============================================================

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Helene Stewart     Answered On: Sep 28

I think this does what you want:

Sub RangeToVariant2()
Dim x As Variant
Dim r As Integer, c As Integer, p As Integer
x = Range("A1:C5").Value
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
If IsNumeric(x(r, c)) Then
x(r, c) = x(r, c) * 2
End If
Next c
Next r
Range("A1:C5") = x
End Sub

range  with 100 columns and say 245 rows still needs only 2
dimensions to gain access to each cell.

 
Didn't find what you were looking for? Find more on Array and Range Or get search suggestion and latest updates.




Tagged: