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!!!!!!

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!!!!!!
|
|

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!!!!!!
|
|