Replace "A2" with Active cell
 

Good day everyone,

Would someone be kind enough to let me know how I could replace "A2"
by the active cell in the following formula:


With ActiveSheet
.Cells(1, 1).End(xlDown).Offset(1).Select
.Range("A2", .Cells(UBound(Arr, 1), UBound(Arr,
2))).Value = Arr
End With

Thank you for your time

Denys

Replace "A2" with Active cell
 

On Wed, 21 Dec 2011 09:07:42 -0800 (PST), Denys wrote:

Good day everyone,

Would someone be kind enough to let me know how I could replace "A2"
by the active cell in the following formula:


With ActiveSheet
.Cells(1, 1).End(xlDown).Offset(1).Select
.Range("A2", .Cells(UBound(Arr, 1), UBound(Arr,
2))).Value = Arr
End With

Thank you for your time

Denys


Not tested but did you just try:

.Range(ActiveCell, .Cells(UBound(Arr, 1), UBound(Arr,2))).Value = Arr

???

Replace "A2" with Active cell
 

Hi Ron,

Yep I tried....no luck

Denys

Replace "A2" with Active cell
 

Denys submitted this idea :
Good day everyone,

Would someone be kind enough to let me know how I could replace "A2"
by the active cell in the following formula:


With ActiveSheet
.Cells(1, 1).End(xlDown).Offset(1).Select
.Range("A2", .Cells(UBound(Arr, 1), UBound(Arr,
2))).Value = Arr
End With

Thank you for your time

Denys

To 'dump' an array into a range you need to pick the top left cell and
resize that to the rows/cols of the array. So...

ActiveCell.Resize(Ubound(Arr, 1), UBound(Arr, 2) = Arr

Also, it's not necessary to select anything and so...

Sub DoStuff()
' Reads a 5Row x 5Col range into an array,
' then dumps the data back into the sheet
' at the 1st empty row below existing data.
Dim Arr
Arr = Range("A1:E5")
Cells(1, 1).End(xlDown).Offset(1).Resize(UBound(Arr, 1), UBound(Arr,
2)) = Arr
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Replace "A2" with Active cell
 

If you want the very last row in column "A" in cases where there might
be blank cells...

Sub DoStuff()
' Reads a 5Row x 5Col range into an array,
' then dumps the data back into the sheet
' at the 1st empty row below existing data.
Dim Arr, v
Arr = Range("A1:E5")
Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(Arr, 1),
UBound(Arr, 2)) = Arr
End Sub

Watch for line wrap...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Replace "A2" with Active cell
 

Thank you Garry for your time....

I'll take a look right now....

Denys

Replace "A2" with Active cell
 

Perfect !!!!

Thank yo very much !!!

Denys

Replace "A2" with Active cell
 

Denys presented the following explanation :
Perfect !!!!

Thank yo very much !!!

Denys

You're welcome! ..glad to be of help!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc