If my active cell is A10 and I have a formula in cell A10 and I would like to copy that formula in the range A1:A9 what would the code be? I would like to use an activecell(offset) format rather then actually spell out the A1:A9 range.
would it be something like Activecell.copy(offset(0, -9))?
Here you go.
See attached for example too.
Please Login or Register to view this content.
Offset works like so:-
Reference:- http://www.excel-vba.com/vba-code-2-6-cells-ranges.htmOffset
The Offset property is the one that you will use the most with Range to move around the sheet.
To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select
--
Regards
PD
----- Don't Forget -----
1. Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
2. Thank those who have helped you by Clicking the scales above each post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
I apologize for the confusion. The formula is in J1(activecell) and I need to copy it over the range I1:A1. The reason why I wanted a simple code to work off the active cell is because I have a large database that the code needs to run on with the same column range (I:A) but in different rows (Every 7 rows). So if I can just figure out how to copy the activecell and then offset one cell to the left of the active cell and paste the formula into the next 9 cells in the row I should be good with a looping macro. I do not believe that your solution will work because it is locking a TestCell, which will change in my database.
So list out your 'TestCells' in a seperate sheet (or within your database). lookup each testcell and run my code? (you'd have to change the range I:A as per above)
There are too many test cells to just list out. Do you know how to use the offset property to copy one cell and then paste it in 10 cells to the left of that cell?
Have a look at http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm for offsetPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks