COPYING A FORMULA IN CELL

guest · Apr 9th, 2002, 12:00 PM

When i copy a formula in varying length of report it copies it till the end if i highlight a column and paste the formula in it. for eg i am concatenating column a & b and the i copy =CONCATENATE($A1,$B1) then i highlight column d and cut and paste it then it copies till the end ie 65000+ rows. problem is at the time of set print it thinks that there are so many rows now how to get rid of this problem and i am using macro

guest · Apr 9th, 2002, 12:10 PM

The message might not be clear so posting again
1. I am trying to copy concatenate formula ie concatenation of col a and b
2. after i copied the formula i select the col so lets say i am trying to paste it in d
so i select d:d at the top and say paste
3. now it paste till the end of sheet ie 65000 rows + i want it to paste only till where there is data. i am doing d:d select because my report is going to be of varying length depending on the data sent into excel.

Any thoughts??
Thanks

guest · Apr 9th, 2002, 12:20 PM

Please help me on this
Thanks

Von Pookie · Apr 9th, 2002, 12:22 PM

Copy the formula you wish to paste.

Press control + G for the goto box.

Type in the range you want to paste to (for example, if your rows go to 500, type d1:d500) and it will select that range. Then paste your code.

Hope this helps,

guest · Apr 9th, 2002, 12:24 PM

but the problem is i dont know the range it could be varying depending on the query results. i am directing the query results into excel and then formatting it using macro

Von Pookie · Apr 9th, 2002, 12:25 PM

Ugh. I missed that part. I unfortunately can't do macros, sorry (wish I could, though).

I'm assuming you may have to add something to your existing macro to do that, though.

guest · Apr 9th, 2002, 12:29 PM

Thanks

guest · Apr 9th, 2002, 12:34 PM

Can anyone help me on this?

guest · Apr 9th, 2002, 01:09 PM

any help will be apreciated

Derek · Apr 9th, 2002, 06:48 PM

Hi Guest

Try this code. It gives a hard value rather than a formula, but it can be changed to a formula if you wish.

For Each cell In [A:A]
If cell.Value <> "" Then
cell.Offset(0, 3).Value = cell.Value & cell.Offset(0, 1).Value
End If
Next
End Sub

If you want to use the concatenate formula use this code. The difference is that the above macro will make a number 1525 by joining 15 and 25 but the concatenate code below gives 1525 as text

For Each cell In [A:A]
If cell.Value <> "" Then
cell.Offset(0, 3).Formula = "=CONCATENATE(RC[-3],RC[-2])"
End If
Next
End Sub

Note that the 3 in the first offset statement refers to column D (3 columns to the right of A), change this number to the number of your destination column.

Hope this helps
regards
Derek

[ This Message was edited by: Derek on 2002-04-09 17:59 ]

Apr 9th, 2002, 12:22 PM	#4
Von Pookie MrExcel MVP, Administrator Join Date: Feb 2002 Location: The act or process of locating. Posts: 13,679	Copy the formula you wish to paste. Press control + G for the goto box. Type in the range you want to paste to (for example, if your rows go to 500, type d1:d500) and it will select that range. Then paste your code. Hope this helps, __________________ Kristy How to post (Video) Show your sheet on the board (HTML Maker FAQ)

Apr 9th, 2002, 12:25 PM	#6
Von Pookie MrExcel MVP, Administrator Join Date: Feb 2002 Location: The act or process of locating. Posts: 13,679	Ugh. I missed that part. I unfortunately can't do macros, sorry (wish I could, though). I'm assuming you may have to add something to your existing macro to do that, though. __________________ Kristy How to post (Video) Show your sheet on the board (HTML Maker FAQ)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 12:00 PM	#1
guest Board Regular Join Date: Feb 2002 Posts: 91	When i copy a formula in varying length of report it copies it till the end if i highlight a column and paste the formula in it. for eg i am concatenating column a & b and the i copy =CONCATENATE($A1,$B1) then i highlight column d and cut and paste it then it copies till the end ie 65000+ rows. problem is at the time of set print it thinks that there are so many rows now how to get rid of this problem and i am using macro

Apr 9th, 2002, 12:10 PM	#2
guest Board Regular Join Date: Feb 2002 Posts: 91	The message might not be clear so posting again 1. I am trying to copy concatenate formula ie concatenation of col a and b 2. after i copied the formula i select the col so lets say i am trying to paste it in d so i select d:d at the top and say paste 3. now it paste till the end of sheet ie 65000 rows + i want it to paste only till where there is data. i am doing d:d select because my report is going to be of varying length depending on the data sent into excel. Any thoughts?? Thanks

Apr 9th, 2002, 12:20 PM	#3
guest Board Regular Join Date: Feb 2002 Posts: 91	Please help me on this Thanks

Apr 9th, 2002, 12:24 PM	#5
guest Board Regular Join Date: Feb 2002 Posts: 91	but the problem is i dont know the range it could be varying depending on the query results. i am directing the query results into excel and then formatting it using macro

Apr 9th, 2002, 12:29 PM	#7
guest Board Regular Join Date: Feb 2002 Posts: 91	Thanks

Apr 9th, 2002, 12:34 PM	#8
guest Board Regular Join Date: Feb 2002 Posts: 91	Can anyone help me on this?

Apr 9th, 2002, 01:09 PM	#9
guest Board Regular Join Date: Feb 2002 Posts: 91	any help will be apreciated

Apr 9th, 2002, 06:48 PM	#10
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Guest Try this code. It gives a hard value rather than a formula, but it can be changed to a formula if you wish. For Each cell In [A:A] If cell.Value <> "" Then cell.Offset(0, 3).Value = cell.Value & cell.Offset(0, 1).Value End If Next End Sub If you want to use the concatenate formula use this code. The difference is that the above macro will make a number 1525 by joining 15 and 25 but the concatenate code below gives 1525 as text For Each cell In [A:A] If cell.Value <> "" Then cell.Offset(0, 3).Formula = "=CONCATENATE(RC[-3],RC[-2])" End If Next End Sub Note that the 3 in the first offset statement refers to column D (3 columns to the right of A), change this number to the number of your destination column. Hope this helps regards Derek [ This Message was edited by: Derek on 2002-04-09 17:59 ]