Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Autofilling to 65543-need macro correction
Hi All,
I am writing a macro to use the data/subtotal tool that excel has. I am inserting a column, and doing a "left" formula in the cell I want that to start with, and then I am autofilling down. The problem is, I need it to autofill to the end of the selection, not the end of excel, which it is doing now....here is the portion of code for the formula and autofilling: ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)" Selection.AutoFill Destination:=Range(ActiveCell, Selection.End(xlDown)) Also, this active cell in column D is at a different location for each wkst I use the macro on, so I can't say anything like D1, or D2, it has to be from the active cell location, and then only to the end of data, which also varies for each worksheet. Any help would be greatly appreciated!!!!!! |
#2
|
|||
|
|||
There are many ways to do this. Here's one:
Sub a() Dim RowCount As Long With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = "=LEFT(RC[-1],3)" .AutoFill .Resize(RowCount) End With End Sub -- Jim "Frantic Excel-er" wrote in message ... | Hi All, | | I am writing a macro to use the data/subtotal tool that excel has. I am | inserting a column, and doing a "left" formula in the cell I want that to | start with, and then I am autofilling down. The problem is, I need it to | autofill to the end of the selection, not the end of excel, which it is doing | now....here is the portion of code for the formula and autofilling: | | ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)" | Selection.AutoFill Destination:=Range(ActiveCell, Selection.End(xlDown)) | | Also, this active cell in column D is at a different location for each wkst | I use the macro on, so I can't say anything like D1, or D2, it has to be from | the active cell location, and then only to the end of data, which also varies | for each worksheet. | | Any help would be greatly appreciated!!!!!! | | |
#3
|
|||
|
|||
YOU ROCK JIM...........
Thank you very much...I have been trying to figure this out for the last 2 days......I have tried it on the a few of the imported documents, and it works nicely... you wouldn't happen to know anything about vlookup and code, would you??? YOU ROCK, YOU ROCK, JIM ROCKS!!!!! "Jim Rech" wrote: There are many ways to do this. Here's one: Sub a() Dim RowCount As Long With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = "=LEFT(RC[-1],3)" .AutoFill .Resize(RowCount) End With End Sub -- Jim "Frantic Excel-er" wrote in message ... | Hi All, | | I am writing a macro to use the data/subtotal tool that excel has. I am | inserting a column, and doing a "left" formula in the cell I want that to | start with, and then I am autofilling down. The problem is, I need it to | autofill to the end of the selection, not the end of excel, which it is doing | now....here is the portion of code for the formula and autofilling: | | ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)" | Selection.AutoFill Destination:=Range(ActiveCell, Selection.End(xlDown)) | | Also, this active cell in column D is at a different location for each wkst | I use the macro on, so I can't say anything like D1, or D2, it has to be from | the active cell location, and then only to the end of data, which also varies | for each worksheet. | | Any help would be greatly appreciated!!!!!! | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro excel save | Excel Discussion (Misc queries) | |||
Saving Excel using Macro | Excel Discussion (Misc queries) | |||
Problem Editing Macro in Shared Excel File | Excel Discussion (Misc queries) | |||
Open Access Database under and Excel window using a Macro | Excel Worksheet Functions | |||
excel macro inconsistency | Excel Discussion (Misc queries) |