COPYING A FORMULA IN CELL

guest

Board Regular
Joined
Feb 21, 2002
Messages
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top