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

 

[alee001]

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

But I need each cells value in arr +1 then transfer to Cells(6, 7).Resize(UBound(arr), UBound(arr, 2)).Value as answer...?

 

[Rick Rothstein]

Have you actually tried any of the solutions that have been posted to see if they in fact do what you are asking for? Or are you just deciding they don't work because you are unfamiliar with what has been proposed?

 

[alee001]

Have you actually tried any of the solutions that have been posted to see if they in fact do what you are asking for? Or are you just deciding they don't work because you are unfamiliar with what has been proposed?

I know how to transfer range value but no idea how to transfer calculated answer by array.

 

[Peter_SSs]

I apologise. I, & I think Rick too, have not taken enough notice of what your code was trying to do.
However, I still don't see the need to individually adjust the 2-dimensional range values and turn them into a 1-dimensional array only to then split them out again into a 2-dimensional range.

Sub sample2()
  Cells(6, 7).Resize(10, 3).Value = Evaluate("B2:D11+1")
End Sub

If you really do want to transfer the values to an array, perhaps to do some other things first before returning them to the worksheet, then you could more easily use a 2-dimensional array the same size & shape as the original and destination ranges.

Sub sample3()
  Dim arr As Variant
  
  arr = Evaluate("B2:D11+1")
  Cells(6, 7).Resize(UBound(arr), UBound(arr, 2)).Value = arr
End Sub

 

[alee001]

I apologise. I, & I think Rick too, have not taken enough notice of what your code was trying to do.
However, I still don't see the need to individually adjust the 2-dimensional range values and turn them into a 1-dimensional array only to then split them out again into a 2-dimensional range.

Sub sample2()
  Cells(6, 7).Resize(10, 3).Value = Evaluate("B2:D11+1")
End Sub

If you really do want to transfer the values to an array, perhaps to do some other things first before returning them to the worksheet, then you could more easily use a 2-dimensional array the same size & shape as the original and destination ranges.

Sub sample3()
  Dim arr As Variant
 
  arr = Evaluate("B2:D11+1")
  Cells(6, 7).Resize(UBound(arr), UBound(arr, 2)).Value = arr
End Sub

Yes, Thank you for you let me know the Evaluate's function and I learn more.

 

[Peter_SSs]

You're welcome. Sorry it took me so long to realise what you were trying to do.

 

[alee001]

You're welcome. Sorry it took me so long to realise what you were trying to do.

No, I can't express it clearly. Your concise code so good. Thanks.