Hi,
I trying to copy 5 cells into one cell with macro in excel 2003. I have an memberlist with adresses in rows and want into one cell in another sheet, but I can't get it right. The result looks like this: John¤Doe¤Street¤
10¤2130¤Oslo
If anyone have an answer to this I am grateful.
Eks:
I have a row looking like this in one sheet:
A B C D E F
315 John Doe Street 10 2130 Oslo
and I wants it to look like this in one cell in another sheet:
A
John Doe
Street 10
2130 Olso
Belowe I have copy of my code but the result is' t what I want:
Sub FlytteAdresser()
Dim LeftCell As Range
Dim RightCell As Range
Dim Value As String 'New DataObject
Dim WorkRange As Range
Dim Val As Integer
Dim userInput As String
With Cells.Range("A:A")
.NumberFormat = ("#,##0")
End With
userInput = Application.InputBox(promp
t:="Velg medlem", Type:=1)
Set WorkRange = Range("A:A")
'Find it and select it
On Error Resume Next
WorkRange.Find(What:=userI
nput, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select
If Err <> 0 Then MsgBox "Medlem finnes ikke"
If IsNumeric(ActiveCell.Value
) Then
Application.ScreenUpdating
= False
Set LeftCell = Cells(ActiveCell.Row, 2)
Set RightCell = Cells(ActiveCell.Row, 6)
If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight)
If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft)
If LeftCell.Column = 256 And RightCell.Column = 1 Then ActiveCell.Select Else Range(LeftCell, RightCell).Select
End If
Selection.Copy
Worksheets("Adresser Uregistrert").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.FormulaR1C1 = " " & Chr(10) & " " & Chr(10) & " "
PasteFormula
Application.CutCopyMode = False
End Sub
Sub CopyFormula()
Dim x As New DataObject
x.SetText ActiveCell.Formula
x.PutInClipboard
End Sub
Sub PasteFormula()
On Error Resume Next
Dim x As New DataObject
x.GetFromClipboard
ActiveCell.Formula = x.GetText
End Sub
Start Free Trial
