I’m using Excel 2007 and want help with creating a macro.

When I paste cells I don’t want excel to replace the content in the destination area. I want Excel to combine the copied cells and the destination cells.

Example:
A1:1
A2:2
A3:3

B1:4
B2:5
B3:6

When A1:A3 is copied, cell B1 is selected and the macro is run the result in B1:B3 should look like this

B1:1/4
B2:2/5
B3:3/6

The macro should work in larger selections as well. Sometimes there will be empty cells in the copy area or the destination area. If there are empty cells the “/”-sign should not be pasted

Any ideas how to solve this?

Hi JJ,

Am Fri, 25 Jan 2013 01:41:59 -0800 (PST) schrieb JJ:

A1:1
A2:2
A3:3

B1:4
B2:5
B3:6

When A1:A3 is copied, cell B1 is selected and the macro is run the result in B1:B3 should look like this

B1:1/4
B2:2/5
B3:3/6

try:

Sub Test()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:B" & LRow).NumberFormat = "# ?/?"
For Each rngC In Range("B1:B" & LRow)
If rngC 0 Then
rngC = rngC.Offset(0, -1) / rngC
End If
Next


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Den fredagen den 25:e januari 2013 kl. 11:00:57 UTC+1 skrev Claus Busch:
Hi JJ,



Am Fri, 25 Jan 2013 01:41:59 -0800 (PST) schrieb JJ:



A1:1

A2:2

A3:3



B1:4

B2:5

B3:6



When A1:A3 is copied, cell B1 is selected and the macro is run the result in B1:B3 should look like this



B1:1/4

B2:2/5

B3:3/6



try:



Sub Test()

Dim LRow As Long

Dim rngC As Range



LRow = Cells(Rows.Count, 2).End(xlUp).Row

Range("B1:B" & LRow).NumberFormat = "# ?/?"

For Each rngC In Range("B1:B" & LRow)

If rngC 0 Then

rngC = rngC.Offset(0, -1) / rngC

End If

Next





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus, your code is working on the exact example I stated above. But if I for example have "1" in C2 and "2" in C3 and I want to copy C2, select C3 and run the macro, it doesn't work.
Also - is it possible to use a solution without formating? I want the "/"-sign to be real.

Hi JJ,

Am Fri, 25 Jan 2013 02:40:32 -0800 (PST) schrieb JJ:

Thanks Claus, your code is working on the exact example I stated above. But if I for example have "1" in C2 and "2" in C3 and I want to copy C2, select C3 and run the macro, it doesn't work.
Also - is it possible to use a solution without formating? I want the "/"-sign to be real.

then try (modify the columns to suit):

Sub Test()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B1:B" & LRow).NumberFormat = "@"
For Each rngC In Range("B1:B" & LRow)
If rngC < "" And rngC.Offset(0, -1) = "" Then
rngC = rngC
ElseIf rngC = "" And rngC.Offset(0, -1) < "" Then
rngC = rngC.Offset(0, -1)
ElseIf rngC = "" And rngC.Offset(0, -1) = "" Then
rngC = ""
Else
rngC = rngC.Offset(0, -1) & "/" & rngC
End If
Next
End Sub

You have not to select cells. The code runs for Column A and Column B
and all the data that is in these columns.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2