How do I programmatically specify a range.

  • Thread starter Thread starter Hari
  • Start date Start date
H

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.
 
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.
 
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)
 
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?
 
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)
 
Back
Top