How do I programmatically specify a range.

[Hari]

Hi,

In column D and row number p , where p = Range("b65536").End(xlUp).Row, I
have a ARRAYFORMULA.

Presently the VBA control is in the above specified cell.

I want to copy this formula in to all cells in the column D starting from
row 2 to row p-1.

So, I wrote the following code, (using macro recorder)

If ActiveCell.Address <> D2 Then
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-6, 0).Range("A1:A7").Select
ActiveCell.Activate
ActiveSheet.Paste

Problem is the in the above case p, the row number was equal to 9, hence
while recording I got the pasting only in D2:D8. How do I make the range
reference dynamic.

I tried with the relative referencing off but again this is also useless to
me.

Selection.Copy
Range("D8").Select
Range(Selection, Selection.End(xlUp)).Select
Range("D2:D8").Select
Range("D8").Activate
ActiveSheet.Paste

Please tell me the correct syntax wherby I could copy the formula in column
D, row P to all the cells above row P except cell D1.

--
Thanks a lot,
Hari
India

[Hari]

Hi,

I tried to do it in a different way (a very long one)

p = Range("b65536").End(xlUp).Row
Range("B65536").End(xlUp).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaArray = "=SUM(IF(ISERROR(FIND(B" & p & ",$A$2:$A$" & i -
1 & ")),0,1))"
q = ActiveCell.Address

If q <> "D2" Then
ActiveCell.Copy
Range("D2").Select
ActiveSheet.Paste
If q <> "D3" Then
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
End If

Im getting an error - Run time error '1004' You cannot change part of an
array.

And the yellow debug line which gets highlighted is the second instance of
activesheet.paste above.

What I understand from above is that if there is an array formula at a cell
then we cannot paste an array formula over it.(Am I right?)

So even this long route has not helped me.

Please suggest a way if possible.

--
Thanks a lot,
Hari
India
"Hari" <excel_hari@hotmail.com> wrote in message
news:uj2jHKI$EHA.2700@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> In column D and row number p , where p = Range("b65536").End(xlUp).Row, I
> have a ARRAYFORMULA.
>
> Presently the VBA control is in the above specified cell.
>
> I want to copy this formula in to all cells in the column D starting from
> row 2 to row p-1.
>
> So, I wrote the following code, (using macro recorder)
>
> If ActiveCell.Address <> D2 Then
> Selection.Copy
> ActiveCell.Offset(-1, 0).Range("A1").Select
> Range(Selection, Selection.End(xlUp)).Select
> ActiveCell.Offset(-6, 0).Range("A1:A7").Select
> ActiveCell.Activate
> ActiveSheet.Paste
>
> Problem is the in the above case p, the row number was equal to 9, hence
> while recording I got the pasting only in D2:D8. How do I make the range
> reference dynamic.
>
> I tried with the relative referencing off but again this is also useless
to
> me.
>
> Selection.Copy
> Range("D8").Select
> Range(Selection, Selection.End(xlUp)).Select
> Range("D2:D8").Select
> Range("D8").Activate
> ActiveSheet.Paste
>
> Please tell me the correct syntax wherby I could copy the formula in
column
> D, row P to all the cells above row P except cell D1.
>
> --
> Thanks a lot,
> Hari
> India
>
>

[Bob Phillips]

Hari,

How about

Cells(p,"D").Autofill Destination:=Range(Range("D2"),Cells(p,"D"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari" <excel_hari@hotmail.com> wrote in message
news:uDu7%23eI$EHA.4004@tk2msftngp13.phx.gbl...
> Hi,
>
> I tried to do it in a different way (a very long one)
>
> p = Range("b65536").End(xlUp).Row
> Range("B65536").End(xlUp).Select
> ActiveCell.Offset(0, 2).Select
> ActiveCell.FormulaArray = "=SUM(IF(ISERROR(FIND(B" & p & ",$A$2:$A$" &
i -
> 1 & ")),0,1))"
> q = ActiveCell.Address
>
> If q <> "D2" Then
> ActiveCell.Copy
> Range("D2").Select
> ActiveSheet.Paste
> If q <> "D3" Then
> ActiveCell.Offset(1, 0).Select
> Range(Selection, Selection.End(xlDown)).Select
> ActiveSheet.Paste
> End If
> End If
>
> Im getting an error - Run time error '1004' You cannot change part of an
> array.
>
> And the yellow debug line which gets highlighted is the second instance of
> activesheet.paste above.
>
> What I understand from above is that if there is an array formula at a
cell
> then we cannot paste an array formula over it.(Am I right?)
>
> So even this long route has not helped me.
>
> Please suggest a way if possible.
>
> --
> Thanks a lot,
> Hari
> India
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:uj2jHKI$EHA.2700@TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > In column D and row number p , where p = Range("b65536").End(xlUp).Row,
I
> > have a ARRAYFORMULA.
> >
> > Presently the VBA control is in the above specified cell.
> >
> > I want to copy this formula in to all cells in the column D starting
from
> > row 2 to row p-1.
> >
> > So, I wrote the following code, (using macro recorder)
> >
> > If ActiveCell.Address <> D2 Then
> > Selection.Copy
> > ActiveCell.Offset(-1, 0).Range("A1").Select
> > Range(Selection, Selection.End(xlUp)).Select
> > ActiveCell.Offset(-6, 0).Range("A1:A7").Select
> > ActiveCell.Activate
> > ActiveSheet.Paste
> >
> > Problem is the in the above case p, the row number was equal to 9, hence
> > while recording I got the pasting only in D2:D8. How do I make the range
> > reference dynamic.
> >
> > I tried with the relative referencing off but again this is also useless
> to
> > me.
> >
> > Selection.Copy
> > Range("D8").Select
> > Range(Selection, Selection.End(xlUp)).Select
> > Range("D2:D8").Select
> > Range("D8").Activate
> > ActiveSheet.Paste
> >
> > Please tell me the correct syntax wherby I could copy the formula in
> column
> > D, row P to all the cells above row P except cell D1.
> >
> > --
> > Thanks a lot,
> > Hari
> > India
> >
> >
>
>

[Hari]

Hi Bob,

Thanx a lot for such an elegant/terse solution.

Just before u posted I somehow made my ends meet with another circuitous
way.


If q <> "D2" Then

ActiveCell.Copy
Range("D2").Select
ActiveSheet.Paste
If q <> "D3" Then
ActiveCell.Offset(1, 0).Select
Range("D3:D" & p - 1).Select
ActiveSheet.Paste
End If
End If

Now , I can substitute my clutter with ur neat code.

One more doubt...

When I saw the way you have used Cells in "Cells(p,"d") I thought of using
it in the following:-

I wanted to write a certain value in Cell B1, so I wrote the following

Cells("B1").Value = "Unique words"

But I get a - Runtime Error '13' type Mismatch

When I replaced the cells by the following

Range("B1").Value = "Unique words"

then it worked. Whats the difference. A cell is the one which has a value,
so why is reffering to Cells("B1").Value considered as incorrect by VBA?
--
Thanks again,
Hari
India

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OKSHL2I$EHA.3616@TK2MSFTNGP11.phx.gbl...
> Hari,
>
> How about
>
> Cells(p,"D").Autofill Destination:=Range(Range("D2"),Cells(p,"D"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:uDu7%23eI$EHA.4004@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I tried to do it in a different way (a very long one)
> >
> > p = Range("b65536").End(xlUp).Row
> > Range("B65536").End(xlUp).Select
> > ActiveCell.Offset(0, 2).Select
> > ActiveCell.FormulaArray = "=SUM(IF(ISERROR(FIND(B" & p & ",$A$2:$A$" &
> i -
> > 1 & ")),0,1))"
> > q = ActiveCell.Address
> >
> > If q <> "D2" Then
> > ActiveCell.Copy
> > Range("D2").Select
> > ActiveSheet.Paste
> > If q <> "D3" Then
> > ActiveCell.Offset(1, 0).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > ActiveSheet.Paste
> > End If
> > End If
> >
> > Im getting an error - Run time error '1004' You cannot change part of
an
> > array.
> >
> > And the yellow debug line which gets highlighted is the second instance
of
> > activesheet.paste above.
> >
> > What I understand from above is that if there is an array formula at a
> cell
> > then we cannot paste an array formula over it.(Am I right?)
> >
> > So even this long route has not helped me.
> >
> > Please suggest a way if possible.
> >
> > --
> > Thanks a lot,
> > Hari
> > India
> > "Hari" <excel_hari@hotmail.com> wrote in message
> > news:uj2jHKI$EHA.2700@TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > In column D and row number p , where p =
Range("b65536").End(xlUp).Row,
> I
> > > have a ARRAYFORMULA.
> > >
> > > Presently the VBA control is in the above specified cell.
> > >
> > > I want to copy this formula in to all cells in the column D starting
> from
> > > row 2 to row p-1.
> > >
> > > So, I wrote the following code, (using macro recorder)
> > >
> > > If ActiveCell.Address <> D2 Then
> > > Selection.Copy
> > > ActiveCell.Offset(-1, 0).Range("A1").Select
> > > Range(Selection, Selection.End(xlUp)).Select
> > > ActiveCell.Offset(-6, 0).Range("A1:A7").Select
> > > ActiveCell.Activate
> > > ActiveSheet.Paste
> > >
> > > Problem is the in the above case p, the row number was equal to 9,
hence
> > > while recording I got the pasting only in D2:D8. How do I make the
range
> > > reference dynamic.
> > >
> > > I tried with the relative referencing off but again this is also
useless
> > to
> > > me.
> > >
> > > Selection.Copy
> > > Range("D8").Select
> > > Range(Selection, Selection.End(xlUp)).Select
> > > Range("D2:D8").Select
> > > Range("D8").Activate
> > > ActiveSheet.Paste
> > >
> > > Please tell me the correct syntax wherby I could copy the formula in
> > column
> > > D, row P to all the cells above row P except cell D1.
> > >
> > > --
> > > Thanks a lot,
> > > Hari
> > > India
> > >
> > >
> >
> >
>
>

[Bob Phillips]

Hari,

Cells wants two arguments, one to denote the row and one for the column, so
that is why Cell("B1") doesn't work. Range expects all of the address in one
argument, which is why Range ("B1") does work. It is this aspect of Cells
that makes it so useful when working with variables, such as Cells(p,"D").
With Range you have to use Range("D" & p), not quite so elegant.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari" <excel_hari@hotmail.com> wrote in message
news:%23Sg4XDK$EHA.2156@TK2MSFTNGP10.phx.gbl...
> Hi Bob,
>
> Thanx a lot for such an elegant/terse solution.
>
> Just before u posted I somehow made my ends meet with another circuitous
> way.
>
>
> If q <> "D2" Then
>
> ActiveCell.Copy
> Range("D2").Select
> ActiveSheet.Paste
> If q <> "D3" Then
> ActiveCell.Offset(1, 0).Select
> Range("D3:D" & p - 1).Select
> ActiveSheet.Paste
> End If
> End If
>
> Now , I can substitute my clutter with ur neat code.
>
> One more doubt...
>
> When I saw the way you have used Cells in "Cells(p,"d") I thought of using
> it in the following:-
>
> I wanted to write a certain value in Cell B1, so I wrote the following
>
> Cells("B1").Value = "Unique words"
>
> But I get a - Runtime Error '13' type Mismatch
>
> When I replaced the cells by the following
>
> Range("B1").Value = "Unique words"
>
> then it worked. Whats the difference. A cell is the one which has a value,
> so why is reffering to Cells("B1").Value considered as incorrect by VBA?
> --
> Thanks again,
> Hari
> India
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OKSHL2I$EHA.3616@TK2MSFTNGP11.phx.gbl...
> > Hari,
> >
> > How about
> >
> > Cells(p,"D").Autofill Destination:=Range(Range("D2"),Cells(p,"D"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Hari" <excel_hari@hotmail.com> wrote in message
> > news:uDu7%23eI$EHA.4004@tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > I tried to do it in a different way (a very long one)
> > >
> > > p = Range("b65536").End(xlUp).Row
> > > Range("B65536").End(xlUp).Select
> > > ActiveCell.Offset(0, 2).Select
> > > ActiveCell.FormulaArray = "=SUM(IF(ISERROR(FIND(B" & p & ",$A$2:$A$"
&
> > i -
> > > 1 & ")),0,1))"
> > > q = ActiveCell.Address
> > >
> > > If q <> "D2" Then
> > > ActiveCell.Copy
> > > Range("D2").Select
> > > ActiveSheet.Paste
> > > If q <> "D3" Then
> > > ActiveCell.Offset(1, 0).Select
> > > Range(Selection, Selection.End(xlDown)).Select
> > > ActiveSheet.Paste
> > > End If
> > > End If
> > >
> > > Im getting an error - Run time error '1004' You cannot change part of
> an
> > > array.
> > >
> > > And the yellow debug line which gets highlighted is the second
instance
> of
> > > activesheet.paste above.
> > >
> > > What I understand from above is that if there is an array formula at a
> > cell
> > > then we cannot paste an array formula over it.(Am I right?)
> > >
> > > So even this long route has not helped me.
> > >
> > > Please suggest a way if possible.
> > >
> > > --
> > > Thanks a lot,
> > > Hari
> > > India
> > > "Hari" <excel_hari@hotmail.com> wrote in message
> > > news:uj2jHKI$EHA.2700@TK2MSFTNGP14.phx.gbl...
> > > > Hi,
> > > >
> > > > In column D and row number p , where p =
> Range("b65536").End(xlUp).Row,
> > I
> > > > have a ARRAYFORMULA.
> > > >
> > > > Presently the VBA control is in the above specified cell.
> > > >
> > > > I want to copy this formula in to all cells in the column D starting
> > from
> > > > row 2 to row p-1.
> > > >
> > > > So, I wrote the following code, (using macro recorder)
> > > >
> > > > If ActiveCell.Address <> D2 Then
> > > > Selection.Copy
> > > > ActiveCell.Offset(-1, 0).Range("A1").Select
> > > > Range(Selection, Selection.End(xlUp)).Select
> > > > ActiveCell.Offset(-6, 0).Range("A1:A7").Select
> > > > ActiveCell.Activate
> > > > ActiveSheet.Paste
> > > >
> > > > Problem is the in the above case p, the row number was equal to 9,
> hence
> > > > while recording I got the pasting only in D2:D8. How do I make the
> range
> > > > reference dynamic.
> > > >
> > > > I tried with the relative referencing off but again this is also
> useless
> > > to
> > > > me.
> > > >
> > > > Selection.Copy
> > > > Range("D8").Select
> > > > Range(Selection, Selection.End(xlUp)).Select
> > > > Range("D2:D8").Select
> > > > Range("D8").Activate
> > > > ActiveSheet.Paste
> > > >
> > > > Please tell me the correct syntax wherby I could copy the formula in
> > > column
> > > > D, row P to all the cells above row P except cell D1.
> > > >
> > > > --
> > > > Thanks a lot,
> > > > Hari
> > > > India
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[medicenpringles]

why not a "do-until" or "for-next" loop?

i'm a noob to those, but it seems if you let it go until the activecell is D65536, then it should do it without too much code.

am i wrong?