Hi gsrikanth,
It is difficult to know what you want, because you don't explain it very well and I have to guess what you mean. Try the following but, if it's not what you want, I'm not going to spend any more time on it unless you give a clear explanation.
Code:
Sub CombineValues()
Dim StrTgt As String, i As Long, j As Long
With ActiveCell
StrTgt = UCase(Left(Trim(InputBox("Combine with cell:" & vbCr & _
"(A)bove" & vbCr & "(B)elow" & vbCr & _
"(L)eft" & vbCr & "(R)ight", "Get Source Direction")), 1))
If StrTgt = "" Then Exit Sub
Select Case StrTgt
Case "A": i = -1: j = 0
Case "B": i = 1: j = 0
Case "L": i = 0: j = -1
Case "R": i = 0: j = 1
Case Else: Exit Sub
End Select
If .Column + i >= 0 And .Row + j >= 0 Then
If IsNumeric(Evaluate(.Offset(i, j).Value)) And IsNumeric(Evaluate(.Value)) Then
.Value = Evaluate(.Offset(i, j).Value) + Evaluate(.Value)
Else
.Value = .Offset(i, j).Value & " " & .Value
End If
.NumberFormat = "General"
End If
End With
End Sub
With this code, simply select a cell and run the macro. It will ask whether you want to combine the cell's contents with those of the cell above, below, left or right.