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

LordTodd215@hotmail.com

Sub abc()
Range("B23").AutoFill Destination:=Range("B23", ActiveCell)
End Sub


--
Regards,
Tom Ogilvy



<Shaka215@gmail.com> wrote in message
news:1145763913.425458.192100@g10g2000cwb.googlegroups.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
>
> LordTodd215@hotmail.com
>

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
>
>
>
> <Shaka215@gmail.com> wrote in message
> news:1145763913.425458.192100@g10g2000cwb.googlegroups.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
> >
> > LordTodd215@hotmail.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. =)

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


<Shaka215@gmail.com> wrote in message
news:1145778872.428355.163970@i39g2000cwa.googlegroups.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. =)
>

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
LordTodd215@hotmail.com


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
>
>
> <Shaka215@gmail.com> wrote in message
> news:1145778872.428355.163970@i39g2000cwa.googlegroups.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. =)
> >

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


<Shaka215@gmail.com> wrote in message
news:1145824120.317544.61880@j33g2000cwa.googlegroups.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
> LordTodd215@hotmail.com
>
>
> 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
> >
> >
> > <Shaka215@gmail.com> wrote in message
> > news:1145778872.428355.163970@i39g2000cwa.googlegroups.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. =)
> > >
>