pastespecial operation in excel

[tunde]

i'm trying to make use of the pastespecial operation in excel to copy the contents of a couple of cells, merge the cells and then paste the combined contents into the merged single cell. however each time the program gets to the point of pasting, it comes up with a "pastespecial method of range class failed" error message. what am i doing wrong here?


Sub mergeCells()
'
' mergeCells Macro
' Macro recorded 31-01-2002 by Tunde Olugbesan
'

Dim str1 As String

str1 = ActiveCell.Value & "" & ActiveCell.Offset(0, 1).Value

ActiveCell.Range("A1:B1&quot.Select
ActiveCell.Range("A1:B1&quot.Copy
Selection.Merge
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd, SkipBlanks:=False, Transpose:=False
End Sub

 

[SkipVought]

Hi,
You are trying to do an arithmatic operation with string values, I think.

What are you trying to accomplish? Concatenate 2 values an d put the result in another cell?

    With ActiveSheet.Cells(1, 1)
        str1 = .Value & .Offset(0, 1).Value
        .Offset(0, 2).Value = str1
    End With

By yhe way, the "" does nothing in your str1 assign code. Skip,
metzgsk@voughtaircraft.com

 

[acron]

If you mean to concatenate the cells and then merge this might help (for the current selection):

Sub CombineCells()
Dim strContents As String
    Application.DisplayAlerts = False
    For Each Cell In Selection
        strContents = strContents & " " & Cell.Value
    Next
    Selection.MergeCells = True
    ActiveCell.Value = strContents
    Application.DisplayAlerts = True
End Sub

AC