Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
Hi there! New to posting messages on the board however, I am an avid
user of reading other peoples questions and figuring out answers from their questions. I came into an interesting problem with getting Excel to do something pretty basic. I need Excel to select a cell and insert the value of that cell (will be a range), into the code...look below. Range("B23").Select "Selection.Autofill Destination:=Range(ActiveCell.Value)" Cell B23 [ VALUE: "B3:B22" ] - will change quite often and the autofill is copying a Vlookup code down to the end of the range. I need this because I am trying to get a spreadsheet to udpate VLOOKUP codes when ever a new item is inserted into this program I am designing. Please assist!! Thanks! Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
Sub abc()
Range("B23").AutoFill Destination:=Range("B23", ActiveCell) End Sub -- Regards, Tom Ogilvy wrote in message oups.com... Hi there! New to posting messages on the board however, I am an avid user of reading other peoples questions and figuring out answers from their questions. I came into an interesting problem with getting Excel to do something pretty basic. I need Excel to select a cell and insert the value of that cell (will be a range), into the code...look below. Range("B23").Select "Selection.Autofill Destination:=Range(ActiveCell.Value)" Cell B23 [ VALUE: "B3:B22" ] - will change quite often and the autofill is copying a Vlookup code down to the end of the range. I need this because I am trying to get a spreadsheet to udpate VLOOKUP codes when ever a new item is inserted into this program I am designing. Please assist!! Thanks! Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
Thanks Tom! That worked great and it did everything it was suppose to
do...however it is filling the rows down to the code even replacing the code with the VLOOKUP fill down. Is there a way to stop this from happening? I suppose I could write another macro to delete the un-wanted fill down but I figured there would be a better way to stop it? Tom Ogilvy wrote: Sub abc() Range("B23").AutoFill Destination:=Range("B23", ActiveCell) End Sub -- Regards, Tom Ogilvy wrote in message oups.com... Hi there! New to posting messages on the board however, I am an avid user of reading other peoples questions and figuring out answers from their questions. I came into an interesting problem with getting Excel to do something pretty basic. I need Excel to select a cell and insert the value of that cell (will be a range), into the code...look below. Range("B23").Select "Selection.Autofill Destination:=Range(ActiveCell.Value)" Cell B23 [ VALUE: "B3:B22" ] - will change quite often and the autofill is copying a Vlookup code down to the end of the range. I need this because I am trying to get a spreadsheet to udpate VLOOKUP codes when ever a new item is inserted into this program I am designing. Please assist!! Thanks! Todd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
Tom it actually turns out that the macro script you gave me is doing
the autofill correctly but its not taking in my target range from CELL B23. I tried to make sense of the VB Code...what part am I missing? PART OF CODE My Understanding (?) ------------------- ------------------------- Range("B23"). Goto this Cell AutoFill Destination: Fill to contents of cell (TARGET ACQUIRED) =Range("B23", STOP FILL ActiveCell) Cell B3? I'm sorry that was the only way I could get clarification to the code you displayed. I know the code isn't pulling in my target range because if I delete cell B23 on the spreadsheet it still will fill in the information... I plan on having the same macro fill in more information in other columns but seeing as how items might be added / removed this will eventually cause a problem because its only filing up to 23 rows...once again thank you for your wisdom, I been trying to figure this out my self the last few days and as always its a really small code to do such a big thing. =) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
My understanding from your description is that you have a formula in B23 and
you want that filled to the activecell. Apparently that isn't what your situation is. If you formula is in B3 and you want to fill to the activecell in column B then you would do Range("B3").Autofill Destination:=Range("B3",ActiveCell) the range on the left is the anchor range and contains the formula. The destination range must start with the anchor range and the last cell to be filled. -- Regards, Tom Ogilvy wrote in message oups.com... Tom it actually turns out that the macro script you gave me is doing the autofill correctly but its not taking in my target range from CELL B23. I tried to make sense of the VB Code...what part am I missing? PART OF CODE My Understanding (?) ------------------- ------------------------- Range("B23"). Goto this Cell AutoFill Destination: Fill to contents of cell (TARGET ACQUIRED) =Range("B23", STOP FILL ActiveCell) Cell B3? I'm sorry that was the only way I could get clarification to the code you displayed. I know the code isn't pulling in my target range because if I delete cell B23 on the spreadsheet it still will fill in the information... I plan on having the same macro fill in more information in other columns but seeing as how items might be added / removed this will eventually cause a problem because its only filing up to 23 rows...once again thank you for your wisdom, I been trying to figure this out my self the last few days and as always its a really small code to do such a big thing. =) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.Autofill Destination:=Range(ActiveCell.Value)
You said it twice, so it must be what you meant:
In B23 I have the value "B3:B12", I need for the autofill macro to see the value in B23 and autofill starting at B3 and going to B12 or whatever the value in B23 is in. I am basically asking the macro to read the value from B23 and autofill to the range specified in B23 (B3:B12). Range(Range("B23")).Value = Range("B23")Value -- Regards, Tom Ogilvy wrote in message ups.com... Tom, Maybe I am not being clear about what it is I need the macro to do... I have a cell (B23) with the parameters I need the autofill to fill to. In B23 I have the value "B3:B12", I need for the autofill macro to see the value in B23 and autofill starting at B3 and going to B12 or whatever the value in B23 is in. I am basically asking the macro to read the value from B23 and autofill to the range specified in B23 (B3:B12). I appreciate your help with this issue. I did create a macro to get rid of the extra code being inserted by having it fill to B1500 but the Excel Workbook is now like 43MB because of the new macro setup. I got the results I was looking for through your original post but the workbook size is very large and I know if the code is tweaked alittle bit it would cut down majorly on the size of the workbook. Once again thank you! -Todd Tom Ogilvy wrote: My understanding from your description is that you have a formula in B23 and you want that filled to the activecell. Apparently that isn't what your situation is. If you formula is in B3 and you want to fill to the activecell in column B then you would do Range("B3").Autofill Destination:=Range("B3",ActiveCell) the range on the left is the anchor range and contains the formula. The destination range must start with the anchor range and the last cell to be filled. -- Regards, Tom Ogilvy wrote in message oups.com... Tom it actually turns out that the macro script you gave me is doing the autofill correctly but its not taking in my target range from CELL B23. I tried to make sense of the VB Code...what part am I missing? PART OF CODE My Understanding (?) ------------------- ------------------------- Range("B23"). Goto this Cell AutoFill Destination: Fill to contents of cell (TARGET ACQUIRED) =Range("B23", STOP FILL ActiveCell) Cell B3? I'm sorry that was the only way I could get clarification to the code you displayed. I know the code isn't pulling in my target range because if I delete cell B23 on the spreadsheet it still will fill in the information... I plan on having the same macro fill in more information in other columns but seeing as how items might be added / removed this will eventually cause a problem because its only filing up to 23 rows...once again thank you for your wisdom, I been trying to figure this out my self the last few days and as always its a really small code to do such a big thing. =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I autofill combo boxes with their destination cell? | Excel Worksheet Functions | |||
Autofill Destination | Excel Programming | |||
Help with "Autofill" Destination Range | Excel Programming | |||
Selection.Copy Destination:= | Excel Programming |