Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace "A2" with Active cell
Hi Ron, Yep I tried....no luck Denys |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace "A2" with Active cell
Thank you Garry for your time....
I'll take a look right now.... Denys |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace "A2" with Active cell
Perfect !!!!
Thank yo very much !!! Denys |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how do I change color of "active" cell to differentiate from rest | Excel Discussion (Misc queries) | |||
Replace(ActiveCell.Formula, "Round(","") not working as expected | Excel Programming | |||
Replace dialog should put focus on "Find What" not "Replace With" | Excel Discussion (Misc queries) |