Dave said:
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).Value) 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
