Paste into a filled cell
 

Is it possible to copy one cell and paste it into another occupied cell
without removing what is already there? Guess we could call that appending
to what is in the existing cell.
Thx
Dave

Paste into a filled cell
 

i would say yes.The answer depends a little bit on what how you want to do
it,and on the "stuff" thats already in the cell.Is this a one off operation
to update some data,or something tha will need to be done all the time or
every day/week/month?Give us an ezample of the data you have got,what you
want to add,and we will on our way!
--
paul

remove nospam for email addy!



"Dave" wrote:

Is it possible to copy one cell and paste it into another occupied cell
without removing what is already there? Guess we could call that appending
to what is in the existing cell.
Thx
Dave

Paste into a filled cell
 

Thanks for the reply Paul -

Lets say A1 is (c*(1+c)^n) and A2 is ((1+c )^n-1) I am currently adding A1
and A2 in A3. It would be handy to copy/paste the contents of A2 into A1
(and add a + sign) and get the result directly.

This are partial examples (i.e., don't make much sense by themselves), but I
have many similar situations. Just looking for an easy way out.

BTW, the point of breaking down these kinds of things is to help
troubleshoot them when they (almost inevitably) don't get the correct result
on the first trial.

Dave

"paul" wrote in message
...
i would say yes.The answer depends a little bit on what how you want to do
it,and on the "stuff" thats already in the cell.Is this a one off
operation
to update some data,or something tha will need to be done all the time or
every day/week/month?Give us an ezample of the data you have got,what you
want to add,and we will on our way!
--
paul

remove nospam for email addy!



"Dave" wrote:

Is it possible to copy one cell and paste it into another occupied cell
without removing what is already there? Guess we could call that
appending
to what is in the existing cell.
Thx
Dave

Paste into a filled cell
 

Dave wrote:
Thanks for the reply Paul -

Lets say A1 is (c*(1+c)^n) and A2 is ((1+c )^n-1) I am currently adding A1
and A2 in A3. It would be handy to copy/paste the contents of A2 into A1
(and add a + sign) and get the result directly.

This are partial examples (i.e., don't make much sense by themselves), but I
have many similar situations. Just looking for an easy way out.

BTW, the point of breaking down these kinds of things is to help
troubleshoot them when they (almost inevitably) don't get the correct result
on the first trial.

Dave

Hi Dave,

I don't know if this macro will do what you want...

Public Sub Append()
Dim rngAppend As Range
Dim rngAccept As Range
Dim rngCellAppend As Range
Dim rngCellAccept As Range
Dim I As Long
Dim blnWrongSelection As Boolean
Do
On Error GoTo CANCELLED
Set rngAppend = Application.InputBox( _
prompt:="Select Cells containing appendices.", _
Title:="Range of Appendices", _
Default:=Selection.Address, _
Type:=8)
On Error GoTo 0
If rngAppend.Areas.Count 1 Then
MsgBox "Cannot do this to a multi-area selection."
End If
Loop While rngAppend.Areas.Count 1
Do
blnWrongSelection = False
On Error GoTo CANCELLED
Set rngAccept = Application.InputBox( _
prompt:="Select Cells to receive appendices.", _
Title:="Range of Receiving Cells", _
Type:=8)
On Error GoTo 0
If rngAppend.Rows.Count < rngAccept.Rows.Count Or _
rngAppend.Columns.Count < rngAccept.Columns.Count Then
blnWrongSelection = True
MsgBox "range of receiving cells must be " & _
rngAppend.Rows.Count & _
" Rows by " & rngAppend.Columns.Count & " Columns"
End If
Loop While blnWrongSelection
For Each rngCellAccept In rngAccept
I = I + 1
Select Case rngCellAccept.HasFormula
Case True
If Not WorksheetFunction.IsNumber(rngAppend.Cells(I).Valu e) Then
rngCellAccept.Value = rngCellAccept.Value & _
" + " & rngAppend.Cells(I).Value
Else: rngCellAccept.Formula = rngCellAccept.Formula _
& "+" & Right(rngAppend.Cells(I).Formula _
, Len(rngAppend.Cells(I).Formula) - 1)
End If
Case False
rngCellAccept.Value = rngCellAccept.Value & _
" + " & rngAppend.Cells(I).Value
End Select
Next
CANCELLED:
End Sub

The user is prompted to select the range of cells containing
appendices, then a range of cells for receiving the appendices (both
ranges must have same numbers of rows and columns).
With receiving cells that have a formula the appendix is added with a
"+" so that the result is still a formula eg receiving cell had formula
=SUM(A1:A10) becomes =SUM(A1:A10)+SUM(B1:B10) if appendix cell had
formula =SUM(B1:B10) or becomes =SUM(A1:A10)+5 if appendix cell is a
constant number. The exception is when the appendix cell is either
constant text or text resulting from a formula, then the receiving
cell's value is concatenated with " + " and the appendix cell value.
With receiving cells that don't have a formula, the receiving cell's
value is concatenated with " + " and the appendix cell value.

Ken Johnson

Paste into a filled cell
 

Whoa !

Holly mackerel, Ken, I'm impressed. I'm not that much on macros, and it's
going to take me a while, but I'll give it a shot. Whether it's what I want
or not, seriously I must say I appreciate the time and talent that went into
tailoring the routine. Very impressive - and I appreciate it very much.

Wish me luck

Dave



"Ken Johnson" wrote in message
oups.com...
Dave wrote:
Thanks for the reply Paul -

Lets say A1 is (c*(1+c)^n) and A2 is ((1+c )^n-1) I am currently adding
A1
and A2 in A3. It would be handy to copy/paste the contents of A2 into A1
(and add a + sign) and get the result directly.

This are partial examples (i.e., don't make much sense by themselves),
but I
have many similar situations. Just looking for an easy way out.

BTW, the point of breaking down these kinds of things is to help
troubleshoot them when they (almost inevitably) don't get the correct
result
on the first trial.

Dave

Hi Dave,

I don't know if this macro will do what you want...

Public Sub Append()
Dim rngAppend As Range
Dim rngAccept As Range
Dim rngCellAppend As Range
Dim rngCellAccept As Range
Dim I As Long
Dim blnWrongSelection As Boolean
Do
On Error GoTo CANCELLED
Set rngAppend = Application.InputBox( _
prompt:="Select Cells containing appendices.", _
Title:="Range of Appendices", _
Default:=Selection.Address, _
Type:=8)
On Error GoTo 0
If rngAppend.Areas.Count 1 Then
MsgBox "Cannot do this to a multi-area selection."
End If
Loop While rngAppend.Areas.Count 1
Do
blnWrongSelection = False
On Error GoTo CANCELLED
Set rngAccept = Application.InputBox( _
prompt:="Select Cells to receive appendices.", _
Title:="Range of Receiving Cells", _
Type:=8)
On Error GoTo 0
If rngAppend.Rows.Count < rngAccept.Rows.Count Or _
rngAppend.Columns.Count < rngAccept.Columns.Count Then
blnWrongSelection = True
MsgBox "range of receiving cells must be " & _
rngAppend.Rows.Count & _
" Rows by " & rngAppend.Columns.Count & " Columns"
End If
Loop While blnWrongSelection
For Each rngCellAccept In rngAccept
I = I + 1
Select Case rngCellAccept.HasFormula
Case True
If Not WorksheetFunction.IsNumber(rngAppend.Cells(I).Valu e) Then
rngCellAccept.Value = rngCellAccept.Value & _
" + " & rngAppend.Cells(I).Value
Else: rngCellAccept.Formula = rngCellAccept.Formula _
& "+" & Right(rngAppend.Cells(I).Formula _
, Len(rngAppend.Cells(I).Formula) - 1)
End If
Case False
rngCellAccept.Value = rngCellAccept.Value & _
" + " & rngAppend.Cells(I).Value
End Select
Next
CANCELLED:
End Sub

The user is prompted to select the range of cells containing
appendices, then a range of cells for receiving the appendices (both
ranges must have same numbers of rows and columns).
With receiving cells that have a formula the appendix is added with a
"+" so that the result is still a formula eg receiving cell had formula
=SUM(A1:A10) becomes =SUM(A1:A10)+SUM(B1:B10) if appendix cell had
formula =SUM(B1:B10) or becomes =SUM(A1:A10)+5 if appendix cell is a
constant number. The exception is when the appendix cell is either
constant text or text resulting from a formula, then the receiving
cell's value is concatenated with " + " and the appendix cell value.
With receiving cells that don't have a formula, the receiving cell's
value is concatenated with " + " and the appendix cell value.

Ken Johnson

Paste into a filled cell
 

Dave wrote:
Whoa !

Holly mackerel, Ken, I'm impressed. I'm not that much on macros, and it's
going to take me a while, but I'll give it a shot. Whether it's what I want
or not, seriously I must say I appreciate the time and talent that went into
tailoring the routine. Very impressive - and I appreciate it very much.

Wish me luck

Dave

Hi Mark,

I wish you all the luck in the world.

Let me know how you go.

Ken

Paste into a filled cell
 

As an aside, I have to translate routines like this into Fortran (mentally)
in order to digest them, then retranslate back into whatever. Kind of like
saying you have to think in a foreign language before you can say you are
fluent. In other words, I'm not fluent in macros - but I'm workin' on it..

Thanks again.

Dave


"Ken Johnson" wrote in message
ups.com...

Dave wrote:
Whoa !

Holly mackerel, Ken, I'm impressed. I'm not that much on macros, and
it's
going to take me a while, but I'll give it a shot. Whether it's what I
want
or not, seriously I must say I appreciate the time and talent that went
into
tailoring the routine. Very impressive - and I appreciate it very much.

Wish me luck

Dave

Hi Mark,

I wish you all the luck in the world.

Let me know how you go.

Ken

Paste into a filled cell
 

1. Copy the cell containing the first formula
2. Select the cell containing the formula to add to
3. Choose Paste Special with the options: Formulas and Add followed by
OK.

Result: Both formulas should appear added and enclosed in parentheses.

Note: this copies formulas in the normal way so that relative
reverences are preserved.
To copy an absolute reference use $A$1 syntax in the formula.

Paste into a filled cell
 

Thanks Lori, I see what you mean. Doesn't like exponentials, tho, one of
those deals where you better know the answer before you're sure you have the
technique right.

Appreciate the help.

Dave


"Lori" wrote in message
ups.com...
1. Copy the cell containing the first formula
2. Select the cell containing the formula to add to
3. Choose Paste Special with the options: Formulas and Add followed by
OK.

Result: Both formulas should appear added and enclosed in parentheses.

Note: this copies formulas in the normal way so that relative
reverences are preserved.
To copy an absolute reference use $A$1 syntax in the formula.

Paste into a filled cell
 

Thank you Lori - I'm working on it. Technique appears to be a little
sensitive to parens, an definitely doesn't like exponentials.
But I'll get it - and thanks so much

Dave

"Lori" wrote in message
ups.com...
1. Copy the cell containing the first formula
2. Select the cell containing the formula to add to
3. Choose Paste Special with the options: Formulas and Add followed by
OK.

Result: Both formulas should appear added and enclosed in parentheses.

Note: this copies formulas in the normal way so that relative
reverences are preserved.
To copy an absolute reference use $A$1 syntax in the formula.