|
How do I programmatically specify a range.
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 |
How do I programmatically specify a range.
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" wrote in message ... 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 |
How do I programmatically specify a range.
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" wrote in message ... 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" wrote in message ... 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 |
How do I programmatically specify a range.
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
How do I programmatically specify a range.
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com