Altered pasting functionality

[JJ]

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 beempty 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?

 

[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

 

[JJ]

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:






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.

 

[Claus Busch]

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