Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the VB code for autofilling relative cells?
I have recorded a macro, and one of my procedures requires using the
autofill feature. Even thought I selected "relative" values when I began recording, the auto fill used the absolute values. Now when I run the macro on worksheets with more data, it only autofills up until the row I recorded the macro with. In other words, I would like to convert this code: Selection.AutoFill Destination:=ActiveCell.Range("A1:A5") to read something like this: Selection.AutoFill Destination:=ActiveCell.Range("A1:The entire length the previous column") Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the VB code for autofilling relative cells?
StarDust,
To match the column to the left of the activecell: ActiveCell.AutoFill Destination:=Range(ActiveCell, _ ActiveCell(1, 0).End(xlDown)(1, 2)) HTH, Bernie MS Excel MVP "stardustsparklin" wrote in message ... I have recorded a macro, and one of my procedures requires using the autofill feature. Even thought I selected "relative" values when I began recording, the auto fill used the absolute values. Now when I run the macro on worksheets with more data, it only autofills up until the row I recorded the macro with. In other words, I would like to convert this code: Selection.AutoFill Destination:=ActiveCell.Range("A1:A5") to read something like this: Selection.AutoFill Destination:=ActiveCell.Range("A1:The entire length the previous column") Many thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the VB code for autofilling relative cells?
Thanks Bernie, the code works great! (I'm Stardustsparklin, but I've created a different profile in thi forum) I was wondering what additional code I would need to select th autofilled data, copy it and paste it over the previous column. Righ now it looks like this: Code ------------------- ActiveCell.AutoFill Destination:=Range(ActiveCell, _ ActiveCell(1, 0).End(xlDown)(1, 2)) ActiveCell.Range("A1:A5").Select Selection.Copy ActiveCell.Offset(0, -1).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ------------------- Obviously, I do not wish to just select A1 through A5. Its a dynami range and the range will change from time to time. Ultimately, what I'm doing is converting the values of column A fro text to number. I start by inserting a column next to it and insertin this formula *=A1/1*. Then I autofill that cell down, copy the values paste them over the original values in the previous column and delet the "temporary" column I had just created. Whew! Is there an easier way to do this in a macro -- faisca_ae ----------------------------------------------------------------------- faisca_aem's Profile: http://www.excelforum.com/member.php...fo&userid=1609 View this thread: http://www.excelforum.com/showthread.php?threadid=27545 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the VB code for autofilling relative cells?
after further investigation i discovered this: Code ------------------- Range(ActiveCell, ActiveCell.End(xlDown)).Selec ------------------- although my code may not be the most efficient, it seems to work fine. :) working code: Code ------------------- ActiveCell.Offset(0, 1).Range("A2").Select Selection.EntireColumn.Insert ActiveCell.FormulaR1C1 = "=RC[-1]/1" ActiveCell.Select ActiveCell.AutoFill Destination:=Range(ActiveCell, _ ActiveCell(1, 0).End(xlDown)(1, 2)) Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Copy ActiveCell.Offset(0, -1).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLef ------------------- -- faisca_ae ----------------------------------------------------------------------- faisca_aem's Profile: http://www.excelforum.com/member.php...fo&userid=1609 View this thread: http://www.excelforum.com/showthread.php?threadid=27545 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for autofilling cells below identically | Excel Discussion (Misc queries) | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions | |||
Relative Cell References within VBA code | Excel Discussion (Misc queries) | |||
autofilling cells | Excel Worksheet Functions | |||
Code to enter formula w/ relative row ref? | Excel Programming |