Range transfer

[alee001]

How to transfer the range to other range/location after calculation? Thanks.

Sub sample()
Dim rng As Range
Dim r As Range
Dim i As Long
Dim arr() As Variant
Set rng = Application.Range("B2:D11")
ReDim arr(rng.Count)
For Each r In rng
    i = i + 1
    arr(i) = r + 1
Next
Cells(6, 7).Resize(10, 3) = arr() 'how to transfer?
Set rng = Nothing
Set arr = Nothing
End Sub

 

[Peter_SSs]

What do you mean by "after calculation"? Tell us a bit more about what you have and what you are trying to achieve.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version[. (Don’t forget to scroll down & ‘Save’)

 

[alee001]

What do you mean by "after calculation"? Tell us a bit more about what you have and what you are trying to achieve.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version[. (Don’t forget to scroll down & ‘Save’)

Thanks reply, I used to Excel 2010 with Win7.
I don't know how to transfer the answer on arr() to Cells(6, 7).Resize(10, 3)?

 

[Peter_SSs]

I used to Excel 2010 with Win7

Please put that in your Account details as I described before, then the information is always available to helpers.

I don't know how to transfer the answer on arr() to Cells(6, 7).Resize(10, 3)?

If that is all you are trying to do, try

Sub TestMove()
  Cells(6, 7).Resize(10, 3).Value = Range("B2:D11").Value
End Sub

 

[Rick Rothstein]

Since your "copy to" range is fixed in location and size, you could also do this...

Sub TestMove2()
  [G6:I15] = [B2:D11]
End Sub

If you are uncomfortable using the square bracket notation, you could also do it this way...

Sub TestMove3()
  Range("G6:I15").Value = Range("B2:D11").Value
End Sub

 

[alee001]

Since your "copy to" range is fixed in location and size, you could also do this...

Sub TestMove2()
  [G6:I15] = [B2:D11]
End Sub

If you are uncomfortable using the square bracket notation, you could also do it this way...

Sub TestMove3()
  Range("G6:I15").Value = Range("B2:D11").Value
End Sub

Not value transfer, why Cells(6, 7).Resize(10, 3) range cannot got the answer?

 

[Rick Rothstein]

Range("G6:I15") and Cells(6,7).Resize(10,3) describe exactly the same range of cells. The shortcut notation for Range("G6:I15") is [G6:I15] and [B2:D11] is a shortcut notation for Range("B2:D11"). Now, with that said, I had an error in my TestMove2 code... I forgot the specify the Value property...

Sub TestMove2()
  [G6:I15] = [B2:D11].Value
End Sub

 

[Peter_SSs]

range cannot got the answer?

Did the range get the answer with the code I posted?

Also, don't forget ..

Please put that in your Account details as I described before, then the information is always available to helpers.

 

[alee001]

You can input any value in Range("B2:D11") then run my simple code, you will found that the range on Cells(6, 7).Resize(10, 3) cannot got the answer by array...

 

[Peter_SSs]

You can input any value in Range("B2:D11") then run my simple code, you will found that the range on Cells(6, 7).Resize(10, 3) cannot got the answer by array...

Yes, we can see that and that is why we have been offering you simple alternative codes that do transfer the data.

If you really must use an array then you can do it like this

Sub sample2()
  Dim arr As Variant
 
  arr = Range("B2:D11").Value
  Cells(6, 7).Resize(UBound(arr), UBound(arr, 2)).Value = arr
End Sub